Неактивная двунаправленная связь

Еще раз о двунаправленных связях, неоднозначности модели и USERELATIONSHIP

TL;DR

Двунаправленные связи в модели данных Power BI и Analysis Services позволяют эффективно решать некоторые проблемы анализа, но могут приводить к неоднозначности – ситуации, когда между таблицами существует более одного пути фильтрации. В таком случае движок DAX пытается при помощи сложного алгоритма выбрать наиболее подходящий путь, и результаты могут оказаться весьма неожиданными для разработчика.

Знание некоторых особенностей работы алгоритма позволяет получать предсказуемые результаты при помощи функций управления связями USERELATIONSHIP и CROSSFILTER.

В этой статье рассмотрю сразу два аспекта работы алгоритма, описание которых не встречалось мне ранее:

  1. Одно из правил выбора пути фильтрации при наличии двунаправленных связей
  2. Влияние этого правила на работу функции USERELATIONSHIP

Проблема двунаправленных связей

В первые несколько лет после запуска Power BI двунаправленные связи создавались в модели данных по умолчанию. Наверное, кто-то в Microsoft решил, что такая фишка будет очень удобна начинающим пользователям, создающим простые модели-звездочки с минимумом таблиц и связей между ними. Но, в конце концов, это стало приводить к нарастающему валу вопросов о «странных» результатах вычислений, и от двунаправленных связей «по умолчанию» было решено отказаться, ко всеобщему благу.

Лучше всего сложности, связанные с двунаправленными связями, описаны в статьях Альберто Феррари и Марко Руссо на сайте sqlbi.com, а также в их книге «Подробное руководство по DAX». Например, вот такая статья «Bidirectional relationships and ambiguity in DAX» и сопутствующее ей видео прекрасно показывают суть проблемы (рекомендую ее прочесть, хотя бы при помощи онлайн-переводчика, перед тем, как двигаться дальше).

Попробую вкратце сформулировать эту проблему так:

  1. Когда связи между таблицами однонаправленные «Один-ко-Многим» (фильтрация всегда движется от стороны связи «Один» к стороне связи «Много»), то такие модели данных обычно не вызывают проблем.
  2. Ситуацию осложняют двунаправленные связи: вполне возможна ситуация, когда фильтрация от одной таблицы к другой может пройти разными путями (неоднозначность связей).
  3. За выбор пути фильтрации в случае неоднозначности отвечает движок DAX, который руководствуется сложной системой правил для определения приоритетного пути.
  4. В некоторых случаях мы можем помочь движку определить необходимый путь фильтрации, используя функции USERELATIONSHIP и CROSSFILTER в мерах.
  5. Если у движка не получается однозначно определить путь, то во время создания связи (или же во время выполнения расчетов) возникает ошибка.

В своей статье Феррари пишет, что алгоритм, который внутри движка DAX решает проблемы неоднозначности, слишком сложный, чтобы его можно было объяснить «на пальцах». Это действительно так, и мне пришлось недавно столкнуться с этим в рабочем проекте. Это «столкновение» подвигло меня на некоторые исследования, которые привели к удивительным выводам.

Два активных пути между таблицами

Для иллюстрации проблемы я сначала возьму один из демонстрационных файлов к главе 15 книги «Подробное руководство по DAX». В этом файле представлена супер-упрощенная модель данных, иллюстрирующая следующую бизнес-проблему:

  1. Учет транзакций ведется в разрезе счетов в таблице 'Transactions'.
  2. Клиент (таблица 'Customers') может управлять несколькими счетами (таблица 'Accounts'), и у одного счёта может быть несколько владельцев.
  3. Для связи клиентов со счетами в таком случае используется таблица-мост 'AccountsCustomers', которая содержит в себе пары значений AccountKeyCustomerKey
Модель данных с двунаправленной связью
Модель данных с двунаправленной связью

Чтобы мы могли в такой модели ответить на вопрос: «Какой оборот по счетам каждого клиента?», мы должны сделать двунаправленной связь между таблицей-мостом 'AccountsCustomers' и таблицей 'Accounts', таким образом, чтобы фильтр от таблицы 'Customers' мог добраться до таблицы 'Transactions'.

Мы можем включить двустороннюю фильтрацию двумя способами:

  • Изменив направление кросс-фильтрации между 'AccountsCustomers' и 'Accounts' на двунаправленное в свойствах связи в модели (как на рисунке), и используя простую меру суммирования по столбцу:

SumOfAmt =
SUM ( Transactions[Amount] )

  • Используя в мере функцию CROSSFILTER с третьим аргументом Both:

SumOfAmt CF =
CALCULATE (
    SUM ( Transactions[Amount] ),
    CROSSFILTER ( Accounts[AccountKey], AccountsCustomers[AccountKey], BOTH )
)

Оба способа дают нам ответ на поставленный выше вопрос:

Оборот по счетам клиента
Оборот по счетам клиента

Так, у клиента Mark суммарный оборот по его двум счетам составил 2800 (800 по личному счету «Mark» и по 1000 по совместно управляемым счетам «Mark-Paul» и «Mark-Robert»).

В этой модели нет видимой неоднозначности связей – единственный путь от 'Customers' до 'Transactions' не создает альтернатив.

Чтобы создать ситуацию неоднозначности, я немного модифицировал эту упрощенную модель, добавив еще две таблицы:

  • 'Agreements' – справочник договоров, заключенных с клиентами. У одного клиента может быть несколько договоров.
  • 'Addendums' – справочник дополнительных соглашений к договорам. У одного договора может быть несколько дополнительных соглашений.

Также я добавил в таблицу 'Transactions' еще один столбец Transactions[AddendumKey], который позволяет определить, в соответствии с каким из дополнительных соглашений была проведена транзакция. Теперь в этой таблице одна строка показывает операцию и в разрезе счёта Transactions[AccountKey], и в разрезе допсоглашения Transactions[AddendumKey].

В итоге модель приобрела вот такой вид:

Измененная модель данных
Измененная модель данных

Этот пример не является отражением лучших практик организации модели, я сделал его таким сознательно с единственной целью – продемонстрировать поведение связей.

Теперь между таблицами 'Customers' и 'Transactions' есть два активных пути – через 'Accounts' и через 'Addendums', и связи в модели очевидно неоднозначные. Попробуйте предположить, не заглядывая вперед, по какому же из путей пойдет фильтрация в данном случае?

Если мы теперь посмотрим на результаты расчетов нашей меры [SumOfAmt], то можем увидеть следующую картину:

Сравнение результатов расчета меры при добавлении новых связей
При добавлении новых связей результат расчета меры изменился

Таблица справа создана в новой модели данных и уже не отвечает на вопрос, поставленный ранее: «Какой оборот по счетам клиента?» Сейчас данные в ней отвечают уже на другой вопрос, который, скорее всего, звучит так: «Какой оборот по счетам клиента с учетом допсоглашений к договорам?»

Очевидно, что в этом случае в действие вступила связь через таблицы 'Agreements' и 'Addendums': несмотря на то, что некоторыми счетами управляют сразу два клиента («Mark-Robert» и «Mark-Paul»), таблица теперь показывает суммы только по допсоглашениям конкретных клиентов. Так, у клиента Mark оборот теперь показывается только по счетам Mark и Mark-Robert, так как операция на 1000 по счету Mark-Paul была проведена по договору клиента Paul. Аналогичная история произошла с оборотами клиента Robert.

Как же понять, почему движком был выбран именно этот путь?

Мы можем увидеть, что эти два активных пути распространения фильтра от 'Customers' имеют одно очень важное отличие: в «верхнем» пути (через 'Accounts') у нас присутствует двунаправленная связь «Многие-к-Одному» между 'AccountsCustomers' и 'Accounts', в то время как в «нижнем» все связи однонаправленные. «Верхний» путь явно проиграл «нижнему» (через 'Addendums') в борьбе за приоритет.

Анализ этой модели и дополнительные изыскания позволили мне сделать вывод о существовании Правила, который подтвердил один из создателей DAX Джеффри Вэнг (Jeffrey Wang):

Путь, в котором фильтрация всегда распространяется только от стороны «Один», будет приоритетнее пути, в котором встречается распространение связи от стороны «Много»

При этом:

  1. Важна именно кардинальность связи на той стороне, откуда распространяется фильтр. Например, двунаправленная связь «Один-ко-Многим» при распространении фильтра со стороны «Один» будут приоритетнее двунаправленной связи «Один-ко-Многим», в которой фильтр распространяется со стороны «Много».
  2. Место, где встречается распространение фильтра от стороны «Много», может быть где угодно в цепочке связей, не обязательно первым на пути следования фильтра.

В нашей модели фильтр от таблицы 'Customers', проходя по связи между 'AccountsCustomers' и 'Accounts', как раз и сталкивается с такой ситуацией – он должен фильтровать таблицу 'Accounts' в направлении от «Много» к «Один». Анализатор связей в таком случае понижает «вес» такой связи, и движок выбирает тот путь, где такие ситуации не встречаются, т.е. путь через 'Addendums'.

Для того, чтобы наша модель и в этом случае позволила нам получить такой же результат, как и ранее (оборот по счетам клиента без учета допсоглашений), мы должны задействовать отключение одной из связей на «нижнем» пути (например, связи между 'Addendums' и 'Transactions‘) при помощи функции CROSSFILTER и ее 3-го аргумента :

SumSumOfAmt Old Path =
CALCULATE (
    [SumOfAmt],
    CROSSFILTER ( Transactions[AddendumKey], Addendums[AddendumKey], NONE )
)

Мы знаем, что фунцкия USERELATIONSHIP позволяет нам выбирать, по какой из связей между двумя таблицами должна идти фильтрация. Можем ли мы использовать эту функцию для получения нужного результата в нашей модели?

Давайте попробуем сделать неактивной одну из связей на «верхнем» пути (например, между таблицами 'AccountsCustomers' и 'Accounts'), и затем активируем ее при помощи функции USERELATIONSHIP:

Неактивная двунаправленная связь
Деактивированная двунаправленная связь

SumOfAmtUR =
CALCULATE (
    [SumOfAmt],
    USERELATIONSHIP ( AccountsCustomers[AccountKey], Accounts[AccountKey] )
)

Как видите, активация связи при помощи USERELATIONSHIP не привела ни к каким изменениям в нашем расчете – фильтрация по-прежнему идет по «нижнему» пути:

Активация связи при помощи USERELATIONSHIP ничего не изменила
Активация связи при помощи USERELATIONSHIP ничего не изменила

В общем-то, трудно было ожидать изменения в данном случае:

  • При неактивной связи фильтр шел по нижнему пути – другого выбора у него не было.
  • USERELATIONSHIP просто активировала отключенную связь «верхнего» пути.
  • Так как никаких изменений в отношении пути между 'Customers' и 'Transactions' через 'Addendums' сделано не было, второй («нижний») путь по-прежнему приоритетен для движка, в соответствии с выведенным нами Правилом.

Таким образом, подводя промежуточный итог, можно сделать следующие выводы:

  • Если вы хотите задействовать определенный путь фильтрации между двумя таблицами, старайтесь избежать неоднозначности в связях модели.
  • Если требования модели данных не позволяют избавиться от неоднозначности, используйте функцию CROSSFILTER для изменения направления кросс-фильтрации связей или их отключения.

Казалось бы – всё на этом, мы разобрались? Отнюдь.

USERELATIONSHIP и выбор единственной связи между таблицами

Следующий пример я сделал на основе реальной задачи анализа взаимосвязей между документами в Dynamics 365 Finance & Operations (AXAPTA). Как правило, в подобных ей ERP-системах существуют сложные системы отношений, которые не всегда (даже в рамках узконаправленного проекта) можно денормализовать до простой схемы «звезда» или «снежинка».

Пусть в нашей модели есть две таблицы:

  • справочник 'Entries', содержащий в себе ссылки на два разных типа документов: на расходный документ в одном столбце и на приходный документ в другом. Столбец расходных документов Entries[Issue] всегда заполнен уникальными значениями кодов документов, а в столбце приходных документов Entries[Receipt] могут встречаться незаполненные значения.
  • 'Documents', содержащий в себе уникальный список документов всех видов и дополнительную информацию, которую нам нужно проанализировать.
Исходные данные
Исходные данные

В модели также присутствуют и другие таблицы. Наша задача – построить связи таким образом, чтобы, приходя по связям из других таблиц, фильтрующих таблицу ‘Entries’, получить из таблицы 'Documents' значения, соответствующие либо расходному, либо приходному документу. Иными словами, фильтр должен распространяться от таблицы 'Entries' к таблице 'Documents' в двух вариантах:

  1. от Entries[Issue] к Documents[DocumentID]
  2. от Entries[Receipt] к Documents[DocumentID]

Когда мы будем создавать первую связь в Power BI, движок проанализирует кардинальность столбцов с обеих сторон и, убедившись, что все значения в столбцах связи уникальные, автоматически создаст связь «Один-к-Одному»:

Автоматически созданная связь один-к-одному
Power BI автоматически создал связь один-к-одному, основываясь на кардинальности столбцов

Неизменяемая двунаправленность этой связи нас вполне устраивает – фильтр вполне может проходить от 'Entries' к 'Documents', и наша задача будет отчасти решена.

Вторую связь (от Entries[Receipt] к Documents[DocumentID]) мы не можем сделать такой же «Один-к-Одному», так как наличие пустых значений в столбце Entries[Receipt] уже свидетельствует о неуникальности значений в нем. Поэтому движок автоматически предложит нам неактивную однонаправленную связь «Многие-к-Одному». Направление кросс-фильтрации от 'Documents' к 'Entries' нас не устраивает – нам надо наоборот. Это легко поправимо – в свойствах связи мы можем установить двунаправленную кросс-фильтрацию:

Две связи между таблицами
Двунаправленная неактивная связь “Многие-к-Одному”

Чтобы получить нужный нам результат, создадим две меры – одна для вычисления суммы по приходным документам, другая – по расходным:

SumOfIssues =
SUM ( Documents[Value] )

SumOfReceipts =
CALCULATE (
    SUM ( Documents[Value] ),
    USERELATIONSHIP ( Documents[DocumentID], Entries[Receipt] )
)

Мы ожидаем, что первая мера покажет нам суммы по расходным документам (связь по умолчанию), а вторая покажет суммы по приходным (за счет активации второй связи). Однако результат далёк от ожидаемого:

USERELATIONSHIP дала тот же результат, что и без нее
Почему не сработала USERELATIONSHIP?

В таблице слева мы получили то, что и должны были – для каждого расходного документа посчитана его сумма, а те документы, которые не нашлись в таблице 'Entries', сгруппировались в пустое значение. Это стандартное поведение.

А вот в правой таблице произошло что-то странное. Если мы еще раз посмотрим на исходные данные, то заметим, что документу E должно соответствовать значение 16, а для F мы должны получить 32. Но мы по-прежнему получили значения для спаренных с E и F расходных документов В и С.

Несмотря на то, что мы активировали связь от столбца Entries[Receipt], движок проигнорировал наш запрос и по-прежнему считает по первой связи – от Entries[Issue]. Это очень странно, неправда ли?

Давайте вспомним, что мы (думаем что) знаем о связях в таком случае:

  1. Между двумя таблицами может быть только одна активная прямая связь (что вполне логично).
  2. Когда мы используем USERELATIONSHIP для активации отключенной связи между таблицами, другие прямые связи между этими двумя таблицами перестают действовать (тоже логично, иначе противоречило бы пункту 1).

Эти два пункта на самом деле работают в абсолютном большинстве случаев (а в Power Pivot на настоящий момент – наверное, в 100% случаев). Но здесь что-то пошло не так…

Чтобы не ходить вокруг да около, я просто еще раз приведу здесь то Правило, которое мы вывели ранее в этой статье:

Путь, в котором фильтрация всегда распространяется только от стороны «Один», будет приоритетнее пути, в котором встречается распространение связи от стороны «Много»

Но почему оно сработало здесь? Ведь у нас нет двух активных путей между таблицами, а активация второй связи при помощи USERELATIONSHIP должна была отключить активную связь «Один-к-Одному» по столбцу Entries[Issue] – ведь так всегда происходит?

Я потратил на изучение этой проблемы много часов, анализируя модель, запросы, планы запросов, мучая коллег и знакомых, и, в конце концов, разработчиков Power BI. Делал я это не из праздного любопытства – приведенный пример был частью большой «боевой» модели данных, с которой я работал. В конце концов Джеффри Вэнг дал мне комментарий, который позволил пролить свет на происходящее.

Итак, приготовьтесь:

  1. При использовании USERELATIONSHIP происходит не буквально «активация одной связи и деактивация другой», а, скорее, увеличение приоритета (веса) неактивной связи над активной на время расчета меры.
  2. Таким образом, в обычной ситуации неактивная связь временно получает более высокий приоритет, чем активная, и распространение фильтрации идет уже по новому пути.
  3. Однако, указанное выше правило в данном случае берет верх и расставляет приоритеты по-своему: так как неактивная связь использует фильтрацию от «Много» к «Один», ее приоритет ниже, чем приоритет связи от «Один» к «Много» (связь «Один-к-Одному» здесь трактуется так же).

Сдвиг парадигмы, неправда ли?

Вы можете расценивать такое поведение как баг, как это склонен был оценивать и я – ведь это противоречит нашим представлениям о прямых связях между таблицами. Но, в данном случае, это проблема актуальности наших представлений об окружающем мире.

Система приоритетов связей была введена в целях корректной обработки сложных формул с многими «нанизанными» друг на друга (или вложенными) USERELATIONSHIP, и это очень интересное решение, которое, однако, привело к вот такому столкновению с алгоритмом обработки неоднозначных связей.

Джеффри назвал это поведение «непоследовательностью», и, по зрелому размышлению, я с ним скорее соглашусь: да, не так, как в других случаях, но это то самое «исключение из правил». Очень маловероятно, что это будет изменено, так как может затронуть большое количество работающих моделей. И, опять же, жаль, что это нигде не было описано до сих пор. Но теперь у вас это знание есть 😊

Что же делать в таком случае – как победить правило и получить требуемый результат?

На самом деле – довольно просто, и для этого есть даже не одно решение:

  • Мы можем использовать в нашей мере в дополнение к USERELATIONSHIP функцию CROSSFILTER, отключающую связь «Один-к-Одному»:

SumOfReceipts CF =
CALCULATE (
    SUM ( Documents[Value] ),
    USERELATIONSHIP ( Documents[DocumentID], Entries[Receipt] ),
    CROSSFILTER ( Documents[DocumentID], Entries[Issue], NONE )
)

  • Мы можем изменить кардинальность активной связи от Entries[Issue] к Documents[DocumentID] на двунаправленную «Многие-к-Одному». Тогда правило определения приоритета столкнется с двумя однотипными связями и ничего не будет делать, оставив право определения приоритета за USERELATIONSHIP
Изменение кардинальности связи
Теперь обе связи имеют одинаковую кардинальность

В обоих случаях мы получим нужный результат – фильтрация заработает так, как нам нужно:

Нужная связь задействована при помощи CROSSFILTER
Использование функции CROSSFILTER дает тот же результат, что и изменение кардинальности связи

Ну и, конечно, мы можем использовать любые варианты виртуальных связей на основе TREATAS, INTERSECT и так далее – с учетом всех связанных с ними нюансов.

В заключение хочу привести еще одну цитату из статьи Альберто Феррари:

The fun part is not in analyzing the numbers; the fun part lies in finding the path that DAX had to discover within the maze to find the exit.

Действительно, DAX нам всегда что-то посчитает (в крайнем случае, выдаст ошибку, если мы грубо ошибемся), но мы должны понимать, что же именно он посчитал. А знание – сила!

Вы можете скачать файл с примерами к данной статье здесь:

Follow me: Facebooktwitterlinkedinmail

Особенность загрузки текстовых столбцов в Power BI и PowerPivot, или Как найти дубликаты там, где их нет

Проблема

На днях я провел несколько увлекательных часов, пытаясь найти причину, по которой Power BI отказывался строить связь «один-ко-многим» между двумя таблицами – справочником и таблицей фактов. Эта короткая история в очередной раз говорит нам: «Век живи – век учись»

Ошибка, на которую указывал Power BI, звучит приблизительно так: «невозможно установить связь – как минимум один из столбцов, участвующих в связи, должен содержать уникальные данные»

Связь между двумя столбцами должна была строиться по текстовому полю [SKU Name], содержащему названия SKU (я понимаю, что это не лучший вариант, но таковы условия проекта), источник данных – таблицы Excel.

Так как в таблице фактов значения не предполагали уникальности, было понятно, что нужно искать проблему именно в справочнике, где они должны быть уникальными.

Вы суслика видите?..

Обнаружить дубликаты в редакторе запросов Power Query не получалось ну совсем никак:

  1. Удаление дубликатов из столбца [SKU Name] не изменило количество строк.
  2. Помня, что модель данных и DAX не различает регистр (в отличие от Power Query), я перевел значения столбца в верхний регистр и снова удалил дубликаты. Не помогло.
  3. Предположив, что в тексте могут случайно встретиться непечатаемые символы, я применил очистку (Text.Clean) значений столбца. Это тоже не помогло удалить дубликаты, но зато увеличилось количество звучащих в моем домашнем офисе непечатных выражений.
  4. Я подключил тяжелую артиллерию – вместо простого удаления дубликатов при помощи кнопки в интерфейсе я видоизменил формулу этого шага, явно указав игнорирование регистра (ну мало ли) и язык проверки дубликатов: Table.Distinct(Table, {{“SKU Name”, Comparer.FromCulture(“en-us”, true)}}). Не помогло…

Обнаружить дубликаты в источнике (файлах Excel) тоже не получилось – Excel упорно уверял меня, что все значения разные.

В конце концов я сдался и сделал то, что нужно было сделать в самом начале – определить, какой же именно элемент (или элементы) справочника модель данных считает дубликатом.

Я создал простую таблицу, в которую поместил список [SKU Name], и во второй столбец поместил тот же [SKU Name], изменив у него вычисление на подсчет значений.

Бинго! Отсортировав второй столбец по уменьшению, я, наконец, увидел злосчастную SKU:

дублирующийся элемент списка
Вот он, исчадие рода Баскервилей SKU!

Осталось только понять, почему же эта SKU считается дубликатом в модели, но не обнаруживается как таковой в Power Query и Excel.

Скопировав название SKU, я вернулся в Power Query и отфильтровал многократно очищенный от непечатаемых символов и дубликатов справочник по этому значению.

Угадаете результат? Правильно, я получил одну строку. Никаких дубликатов.

Здесь должно быть две строки, но видна только одна
Здесь должно быть две строки, но видна только одна

Я на некоторое время завис, не понимая, что делать дальше. «Вы суслика видите? – Нет. – А он есть.»

Не дубликат я!

И тут меня посетила гениальная идея: вместо фильтра столбца по самому значению SKU, я решил проверить, что мне даст фильтр «Текст содержит…»

Вот они, красавицы:

Дубликаты нашлись. Осталось понять, почему они не удалились
Дубликаты нашлись. Осталось понять, почему они не удалились

В общем, опустив еще несколько непечатных выражений, резюмирую: эти две строки отличаются лишним пробелом в конце в одной из строк.

Еще немного экспериментов показали:

  1. При загрузке в модель данных из строк удаляются концевые пробелы.
  2. Начальные пробелы и лишние внутренние пробелы не удаляются
  3. PowerPivot в Excel ведет себя точно также, как и Power BI
  4. Обычная сводная таблица в Excel уважает концевые пробелы и не удаляет их.

Вы знаете, вообще-то предупреждать надо… Я перерыл кучу документации и нашел только несколько отсылок к кубам Analysis Services, а также несколько сообщений на форумах, подтверждающих: так задумано, и вряд ли будет изменено в ближайшем времени. Может быть, кто-то сможет найти ссылку на место в документации, где об этом говорится?

Опытные товарищи подсказали, что это поведение соответствует стандарту ANSI SQL. Возможно, это и так, но мне, как пришедшему из мира Excel, это неведомо.

Резюме

Справиться с этой проблемой можно так:

  1. Если такие строки действительно дубликаты, они прекрасно удаляются за счет применения функции Text.Trim (или, если вам дороги начальные пробелы, функции Text.TrimEnd) перед удалением дубликатов. Это можно сделать и через интерфейс, кнопками, на вкладке Transform:
    Удаляем лишние пробелы (с обоих концов строки)
  2. Если эти строки принципиально различаются и вы не хотите считать их дубликатами, то нужно сделать очень простую вещь – добавить какой-то символ к строке, заканчивающейся на пробел.
    Например, можно (в новом столбце или при трансформации столбца) добавить точку в конец такой строки при помощи простой функции:
    (string)=> if Text.End( string, 1 )=" " then string &"." else string
  3. Если вы не против добавить символ ко всем строкам, то можно просто добавить суффикс ко всем значениям столбца – через то же меню, что и на картинке выше, пункт “Add Suffix”.

Удачи, и глядите в оба!

Follow me: Facebooktwitterlinkedinmail

Пользовательская сортировка каскадной диаграммы (Waterfall) в Power BI

Недавно в чате о Power BI в Telegram был задан вопрос – возможно ли применить для графика Waterfall (каскадная диаграмма, она же «Водопад») нестандартную динамическую сортировку: положительные значения показываются по убыванию, а отрицательные наоборот, по возрастанию (то есть, сначала самые большие по модулю отрицательные значения, затем минуса поменьше и самые мелкие – в конце).

В итоге из такого графика:

Обычная сортировка каскадной диаграммы

Нужно получить вот такой:

Нестандартная сортировка

Стандартными средствами мы можем сортировать Waterfall только по возрастанию или убыванию, по обычным правилам (настроить и проверить сортировку можно нажав на три точки в правом верхнем углу визуала):

Настройка сортировки в каскадной диаграмме Power BI

Нам же нужно сделать так, чтобы:

  1. Можно было применить нестандартную сортировку;
  2. Она должна быть динамической, то есть реагировать на фильтры (например, по дате)

Решение этой задачи делится на две подзадачи:

  1. Найти способ применить пользовательскую сортировку
  2. Определить и реализовать алгоритм сортировки

Можем ли мы применить пользовательскую сортировку?

Первая подзадача в случае визуального элемента Waterfall (каскадная диаграмма) решается просто: мы можем использовать для сортировки любые поля, помещенные в одну из областей визуального элемента.

У Waterfall таких областей четыре:

  1. Категория
  2. Распределение
  3. Ось Y
  4. Подсказки

Первые две используются для определения значений оси X, третья – для определения размера столбиков, а вот четвертая используется для вывода информации во всплывающем окошке при наведении мыши на элемент графика.

Вот ее мы и будем использовать для того, чтобы отсортировать значения на графике в нужном нам порядке.

Скажу сразу – если бы мы не могли использовать эту область для сортировки, то и решение было бы кардинально другим, если бы вообще было.

Как задать правило сортировки?

Вспомним, что сортировка должна учитывать возможность применения пользовательских фильтров, поэтому нам не подойдет вычисляемый столбец в DAX или Power Query). То есть, нам нужна мера.

Теперь нам надо придумать, как создать такую формулу, которая будет давать нам нужный порядок сортировки.

Continue Reading Follow me: Facebooktwitterlinkedinmail

Связи многие-ко-многим в Power BI

В обновлении Power BI Desktop от 22 июля 2018 появилась новая замечательная возможность – создавать связи “многие-ко-многим” (Many-To-Many, или M2M) между таблицами модели данных. Пока еще в предварительной версии, то есть не работающее в Power BI Service, но очень интересное нововведение.

До этого обновления в Power BI можно было создавать связи только двух видов: “один к одному” и “многие-к-одному“. Новый тип связи появился в связи с введением в пробную эксплуатацию так называемой “композитной”, или “составной” модели, позволяющей использовать в проекте одновременно как источники, подключенные в режиме Direct Query (например, MS SQL Server), так и источники в режиме Import (например, файл Excel), или сразу несколько источников Direct Query. Связи “многие-ко-многим” на текущий момент являются единственным способом связи между источниками, подключенными в разных режимах (DQ и Import), независимо от их фактической кратности.

Однако применение нового типа связи “многие-ко-многим” не ограничивается только рамками композитной модели – такие связи теперь можно устанавливать между любыми таблицами Power BI!

Такой тип связей открывает для разработчика новую степень свободы, если можно так выразиться. Теперь не обязательно создавать промежуточные бридж-таблицы для связывания двух таблиц по неуникальному ключу – можно настроить такую связь напрямую. Это уменьшает количество таблиц и связей между ними, делая модель проще.

Реализация связей “многие-ко-многим” в Power BI сопряжена с рядом ограничений. Сейчас их всего три (они действуют именно для таблиц, связанных таким способом):

  1. Невозможно использовать функцию RELATED для получения данных связанной таблицы (так как связанными могут оказаться несколько строк).
  2. Не создаются пустые строки для группировки строк, отсутствующих в связанной таблице (а также для строк, имеющих Null в столбце связи другой таблицы).
  3. Функция ALL(), примененная к одной из таблиц, не сбрасывает фильтры, примененные к связанной таблице (а, например, в связи “один-ко-многим” функция ALL(Table) сбрасывает все фильтры со столбцов таблицы, в том числе фильтры, примененные к столбцам таблиц, связанных с находящихся на стороне “один”).

Эти ограничения не так очевидны, и неподготовленный аналитик может быть неприятно удивлён неожиданному поведению мер и визуальных элементов.

Достаточно подробно эти ограничения описаны в официальной документации, а мы с Максимом Уваровым практически сразу после выхода обновления записали видео, в котором я постарался подробно рассказать о новом типе связей, его ограничениях и подводных камнях.

Я считаю, что появление составной модели и нового вида связи между таблицами – это крупнейшее изменение в моделировании данных в Power BI  после введения двунаправленной фильтрации. И хотя пока этот тип связи вызывает много вопросов, меня однозначно радует всё, что касается новых инструментов подготовки данных и моделирования в Power BI: приятно смотреть, как любимый инструмент развивается и становится всё мощнее и мощнее. А судя по опубликованному roadmap до октября 2018 года, планов относительно развития Power BI у Microsoft очень много. Нет, не так. ОЧЕНЬ МНОГО.Follow me: Facebooktwitterlinkedinmail


Relative reference in calculated column

Абсолютные и относительные ссылки в Power Query – подход в стиле Excel

Эта статья – перевод моего первого поста в этом блоге, который был опубликован 5 ноября 2015 года на английском языке. К моему удивлению, этот пост – самый популярный, за это время он набрал почти 21000 просмотров. С небольшими стилистическими правками публикую его на русском языке. В переводе помогал мой сын Дмитрий, за что ему отдельное спасибо.

Power Query – это мощный инструмент, способный на большее, чем просто брать данные из источника и переносить их в таблицу или Power Pivot. Данные можно очищать и преобразовывать множеством способов, но есть некоторые действия, привычные для Excel, которые не так удобно делать в Power Query.

Например, что, если мне нужна относительная ссылка на конкретную ячейку в таблице Power Query – значение из определённой строки в определённом столбце? Или ссылка на значение в определённом столбце на четыре строки выше? В Excel очевидно, как это сделать: нужно просто указать на нужное значение мышкой, убедиться, что из ссылки к строке убран знак “$” (знак абсолютной ссылки), и всё. Но в Power Query я не могу так просто это сделать.

Но всё же решение, хотя и непрямое, существует.

Прежде всего давайте выясним, как можно получить доступ к конкретному значению из таблицы Power Query.

Простейший способ понять адресацию в Power Query, по-моему, анализировать код шагов.

Абсолютные ссылки на строки

Допустим, у нас есть простая таблица из двух столбцов: даты (Date) и количества (Amount). В ней пять строк, и в первом столбце стоят, как ни странно, даты, во втором – какие-то значения:

Absolute and Relative References in Power Query

Исходные данные

Мы хотим получить значение из ячейки B4, а именно 120. Continue Reading Follow me: Facebooktwitterlinkedinmail


Сравнение значений null в Power Query

Недавно мне нужно было сделать очень простую операцию в Power Query. В столбце с числами нужно было выполнить проверку “значение меньше N” и в новом столбце вывести соответствующий текст. Функция дополнительного столбца выглядит примерно так:

= if [Values] < 5 then "A" else "B"

На самом деле некоторые значения – null (то есть пустые):

Данные содержат null и в результате сравнения возникает ошибка

И такая простая операция возвращает ошибку для этих значений!

Почему? Есть некоторая ловушка, спрятанная в глубинах документации (а именно на странице 67 PDF-файла  “Power Query Formula Language Specification (October 2016)”, который можно найти тут.

Continue Reading Follow me: Facebooktwitterlinkedinmail


Номер недели ISO в Power Query / M и Power BI

Иногда, особенно во время работы с таблицами-календарями, нам необходимо определить номер недели по ISO. К сожалению, “родной” функции для этого в Power Query или в Power BI нет, и для получения нужного результата приходится писать свою.

Спасибо Catherine Monier, Microsoft Excel MVP, за ссылку на готовую функцию для Power Query “Date to ISO Week”. Также по этой ссылке можно найти и обратную функцию, переводящую даты формата 2017-W02-7 в обычную дату:

Написать такую функцию не очень сложно, но приятно же, когда это уже сделали за вас? 🙂Follow me: Facebooktwitterlinkedinmail


Как получить несколько значений в результате запроса Power Query

Эта статья о работе надстройки Power Query к Excel 2010 и Excel 2013, редактора запросов Power BI и  группы команд “Get & Transform” (“Получить и преобразовать”) в Excel 2016. Надеюсь, когда-нибудь эта чехарда закончится и мы сможем говорить просто Power Query.

Результатом вычисления запроса в Power Query является единственное значение. Как правило, речь идет о таблице, которую мы затем выгружаем на лист или в модель данных в Excel и Power BI. Это же требование относится также и к любым другим выражениям, вычисляемым Power Query, например, встроенным или пользовательским функциям, полям записей, записям в целом, и т.д.

Однако можно легко представить себе ситуацию, когда нам нужно, чтобы функция или запрос вместе с основным результатом вернули и другую информацию – дополнительный или промежуточный результат вычисления.

Промежуточные и дополнительные результаты запросов

Представьте, что в процессе сложных преобразований запроса Query1 последним шагом под названием ResultTable  мы получили нужный результат (таблицу), которую мы хотим загрузить в модель данных. Одним из промежуточных шагов в нашем запросе был расчет какой-то величины ValueX , и мы хотели бы использовать ее в других выражениях или запросах:

// Query1
let
    Source = …, // источник данных
    …,    // какие-то шаги
    ValueX = …, // промежуточный результат
    …,    // еще какие-то шаги
    ResultTable = …     // получаем таблицу-результат
in
    ResultTable    // но она не содержит в себе данные для получения ValueX

Нам в итоге нужен и ValueX , и, конечно же, ResultTable .

У нас есть как минимум три способа это сделать: Continue Reading Follow me: Facebooktwitterlinkedinmail


Объединяем выбор на разных срезах в DAX: Динамический фильтр “ИЛИ”

В стандартном режиме несколько срезов в Power BI работают по принципу «И», то есть пересечения примененных фильтров. Мы выбираем на одном срезе «Яблоки», на другом цвет «Красный», и получаем меры, рассчитанные только для красных яблок. Зеленые яблоки будут проигнорированы.

Обычный срез: красные яблоки

Обычный срез: красные яблоки

Периодически (хоть и нечасто) возникает другая задача: как заставить срезы работать по принципу «ИЛИ», то есть учитывать в мере значения обоих срезов? Например, получить одновременно все зеленые фрукты и все яблоки? Все желтое или грушевидное?

Более практичный пример: товарная позиция может быть помечена в базе как складская (за это отвечает свойство «Складская»), одновременно она помечена как плановая (за это отвечает другое свойство, «Плановая»). Для расчетов нас интересуют позиции, которые могут являться, к примеру, складскими ИЛИ плановыми (то есть у них может быть установлено либо одно из этих свойств, либо оба).  Но при использовании двух обычных срезов отбор по свойству “Складская” = “Да” приведет к тому, что прочие строки будут отфильтрованы, даже если у них свойство «Плановая» тоже установлено срезом в значение «Да».

Стандартное решение

Множество интересных способов получить желаемый результат можно почерпнуть в статье гуру DAX Альберто Феррари и Марко Руссо («итальянцев»). Обычно примеры таких мер используют так называемые «прямые» фильтры – когда проводится сравнение с жестко заданным значением. Стандартное решение для таких мер следующее:

Continue Reading Follow me: Facebooktwitterlinkedinmail


Первая встреча Power BI User Group в Санкт-Петербурге

Идея собрать петербургское сообщество пользователей Power BI, вынашиваемая мной и Дмитрием Соловьевым с весны, к концу лета наконец-то “нашла свое воплощение” благодаря настойчивости Димы, помощи коллег из Awara IT Solutions и поддержке самой популярной русскоязычной группы в Facebook, посвященной Power BI

В итоге при поддержке Microsoft в Санкт-Петербурге 23 августа 2017 года прошла первая встреча SPb Power BI User Group (с параллельным созданием сообщества на сайте https://www.pbiusergroup.com).

Состав выступающих был весьма плотный, участвовали целых 5 спикеров:

Я насчитал более 30 человек слушателей – очень неплохо для первого раза и скромного помещения!

Тема моего выступления была достаточно замысловато задана как “Меры DAX с нестандартной реакцией на срезы”. На самом деле рассказ был о том, как:

Continue Reading Follow me: Facebooktwitterlinkedinmail