Подробное руководство по dax torrent

Чтобы превратить отчеты Power BI и Excel в настоящий инструмент аналитики, вам потребуются DAX-формулы. Используя эти формулы, можно настраивать вычисления в отчетах один раз, а при изменении исходных данных в отчете показатели будут пересчитаны. В Excel для этого потребуется нажать только одну кнопку — «Обновить все».

В этой статье мы собрали самые полезные и часто встречающиеся DAX-формулы. Какие именно формулы выбрали? Все, что доступно пользователю, перечислять смысла нет. А вот несколько формул, которые позволят решить 80% задач аналитики и пригодятся для оформления отчетов, рассмотреть стоит. Например, как показать остатки на конец периода, а не сумму всех остатков, как обычно получается в сводной таблице. Или как посчитать нарастающие итоги.

Кстати, в предыдущей статье «Введение в вычисления DAX» есть примеры других полезных и более простых формул – SUM, MIN, MAX, DIVIDE, BLANK и др. Если вы ещё не читали эту статью, советуем начать с неё.

Для удобства формулы в статье разделены на две части.

1. CALCULATE

CALCULATE рассчитывает выражение с учетом измененного контекста фильтра.
Это самая главная формула в языке DAX. Ее название так и переводится – «вычислить», поэтому без такой формулы отчеты точно сделать не получится. Записывается формула просто:

CALCULATE ( <выражение> [, <фильтр1>, <фильтр2> … ] )

В вычислениях CALCULATE часто используют вместе с функциями фильтрации, например, с FILTER. Или с модификаторами – USERELATIONSHIP, REMOVEFILTERS и др. Что это за функции и как они работают – смотрите дальше.

Пример: посчитать выручку по заказам в указанном регионе.

2. FILTER

FILTER выбирает данные в таблице или в выражении по заданному критерию. Результат ее вычисления — таблица. Синтаксис функции:

FILTER ( <таблица>, <фильтр> )

В мерах FILTER обычно используют вместе с другими формулами, например, вместе с CALCULATE, CONTROWS и др.

3. ALL

В отличие от FILTER, которая ограничивает выборку данных, функция ALL делает наоборот. ALL снимает примененные к таблице или столбцу фильтры, и позволяет создавать вычисления по всем строкам таблицы или по всем значениям столбца. Записывается формула просто:

ALL ( <таблица> или <столбец> [, <столбец1> ,<столбец2>, … ] )

Так же как FILTER, функция ALL используется с другими формулами, например с CALCULATE. Если вам нужно убрать фильтры не со всей таблицы полностью, а только с некоторых столбцов, можно воспользоваться похожей формулой – ALLEXCEPT. С помощью ALL можно делать различные вычисления – найти долю от общего объема продаж, соотношения или проценты и т.д.

Пример: найти долю выручки от продаж товара.

С помощью DIVIDE выручка по товару делится на общий итог от продаж. Для расчета общего итога как раз и используется формула ALL – она снимает все фильтры, примененные к справочнику товаров. Точно такой же результат получится, если вместо ALL использовать функцию REMOVEFILTERS, о которой написано дальше.

Поведение функции ALL можно проверить с помощью среза по товарам. Если в срезе поставить галочки рядом с некоторыми товарами, то видно, что сумма процентов в итоговой строке таблицы не равна 100%. Доля товара считается без учета того, выбраны или нет товары в срезе. Если нужно посчитать долю только с учетом выбранных товаров – так, чтобы итоговая сумма процентов в таблице всегда была равна 100%, вместо ALL используйте ALLSELECTED.

4. SUMX

Для чего нужна формула SUMX? Она позволяет суммировать значения выражения по строкам. Синтаксис формулы:

SUMX ( <таблица>, <выражение> )

Если вам нужно просто посчитать сумму по столбцу, используйте формулу SUM. Если нужно рассчитать построчно показатели в таблице и затем сложить их, вам потребуется SUMX.

В таблице в каждой строке количество умножается на цену, а полученные результаты суммируются. В данном случае формула SUMX сработала очень похоже на «обычную» формулу Excel – СУММПРОИЗВ.

5. CALENDARAUTO

Таблица, без которой точно не обойдется ни один отчет в Power BI и Power Pivot – это таблица дат, Календарь. С помощью такой таблицы можно создавать аналитику по периодам и делать вычисления, связанные с датами.

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

Создается календарь (таблица дат) в Power BI просто:

Календарь = CALENDARAUTO()

Также таблицу дат в Power BI можно создать с помощью CALENDAR, но в этой формуле потребуется указать границы – наибольшую и наименьшую дату.

А вот в Power Pivot в Excel таких формул для календаря нет. Так же, как нет возможности создавать вычисляемые таблицы. Поэтому календарь в Power Pivot можно сделать с помощью специальной кнопки «Таблица дат» (для этого даже предусмотрена специальная кнопка) или с помощью загрузки из Power Query.

6. FORMAT

Для создания «правильной» таблицы дат в Power BI вам пригодится формула FORMAT. Функция FORMAT преобразует значение в текст в указанном формате. Это аналог формулы ТЕКСТ в «обычном» Excel.
Синтаксис формулы:

FORMAT ( <значение>, <формат строки> )

Пример: определить дни недели для дат.

День недели = FORMAT ( [Дата], "DDD" )

Примеры форматов строк для дат, записываются в кавычках:

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

7. DATEADD

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

DATEADD ( <даты>, <количество интервалов>, <вид интервала> )

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

Пример: рассчитать значение показателя за прошлый год.

8. TOTALYTD

Формула TOTALYTD вычисляет значение выражения с начала года в текущем контексте. То есть с помощью TOTALYTD можно посчитать нарастающий итог по показателю за год. Чтобы рассчитать нарастающие итоги по кварталам или месяцам, вам потребуются похожие формулы: TOTALQTD для квартала и TOTALMTD для месяца.
Записывается формула просто:

TOTALYTD ( <выражение>, <даты> [, <фильтр> ] [, <конец года>] )
TOTALQTD ( <выражение>, <даты> [, <фильтр> ] )
TOTALMTD ( <выражение>, <даты> [, <фильтр> ] )

9. LASTNONBLANK

LASTNONBLANK определяет последнее непустое значение в столбце. Чтобы найти первое непустое значение в столбце – FIRSTNONBLANK. Синтаксис формул:

FIRSTNONBLANK ( <столбец>, <выражение> )
LASTNONBLANK ( <столбец>, <выражение> )

С помощью этих функций, например, можно посчитать остатки на начало и конец периода. Формула LASTNONBLANK подходит для вычислений, когда остатки не указаны на последний день периода.

Пример. Найти остатки ТМЦ на конец периода.

10. CLOSINGBALANCEYEAR

CLOSINGBALANCEYEAR вычисляет значения выражения для последней даты года. Аналогично расчет для последней даты квартала – CLOSINGBALANCEQUARTER, для месяца — CLOSINGBALANCEMONTH. Так можно рассчитать остатки товаров или денег на счете на конец периода.

CLOSINGBALANCEYEAR ( <выражение>, <даты> [, <фильтр> ][, <конец года> ] )
CLOSINGBALANCEQUARTER ( <выражение>, <даты> [, <фильтр> ] )
CLOSINGBALANCEMONTH ( <выражение>, <даты> [, <фильтр> ] )

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

Пример: найти сумму значений на последнюю дату месяца.

сумма к.п. = CLOSINGBALANCEMONTH ( SUM ( 'данные'[значение] ), 'Календарь'[Date] )

Следующие формулы в статье используются в построении отчетов не так часто, как те, о которых написано выше. Их понимание необходимо для создания развернутой аналитики и оформления, а также для более полного погружения в язык DAX.

11. VALUES

Функция VALUES возвращает значения столбца или таблицы:

• если указать столбец, то VALUES вернет список его уникальных значений;
• если как аргумент указать таблицу, то формула вернет список всех строк таблицы с сохранением повторений.

При вычислениях с VALUES примененные фильтры сохраняются и возвращаются видимые в текущем контексте значения, а пустые строки учитываются в расчетах. Обычно VALUES используется как промежуточная формула внутри других вычислений. Записывается формула просто:

VALUES ( <таблица или столбец> )

Пример: посчитать, сколько менеджеров работало в каждом городе.

Формула ALL игнорирует фильтры, поэтому в строках одинаковые цифры. Формула VALUES сохраняет фильтры и выдает правильный результат.

12. KEEPFILTERS

KEEPFILTERS – это еще одна полезная формула, которую используют вместе с CALCULATE и CALCULATETABLE. При вычислениях формула CALCULATE перезаписывает существующие фильтры в столбцах отчета. А если перезапись фильтров не требуется, и к действующим нужно просто добавить новые фильтры, это можно сделать с помощью KEEPFILTERS. Формула записывается так:

KEEPFILTERS ( <выражение> )

Пример: посчитать выручку по определенной группе товаров.

Если записать формулу для вычисления так:

выручка = CALCULATE ( [выручка], 'спТовары'[товар] = "коньки" )

то сумма по выбранной группе запишется в каждой строке таблицы. KEEPFILTERS меняет поведение стандартное поведение формулы CALCULATE и сохраняет уже примененные фильтры – выручка по товару будет заполнена только в нужной строке:

13. REMOVEFILTERS

REMOVEFILTERS очищает фильтры в указанной таблице или столбце.
Синтаксис формулы:

REMOVEFILTERS ( [ <таблица> или <столбец>[, <столбец>[, …] ] ] )

С помощью этой функции нельзя получить «промежуточную» таблицу, а можно только убрать фильтры. Формула REMOVEFILTERS применяется в вычислениях с CALCULATE. Например, с ее помощью можно посчитать долю (%) продаж товара от общей суммы продаж, как в примере с ALL.

Пример: найти долю от продаж товара по отношению к общей сумме выручки.

Что лучше использовать для вычислений в этом примере – ALL или REMOVEFILTERS? Если в приложении доступна формула REMOVEFILTERS, и в формуле вам просто нужно очистить фильтры, то рекомендуется использовать REMOVEFILTERS.

14. ISFILTERED

Формула ISFILTERED определяет, применен ли прямой фильтр к столбцу. Если такой фильтр есть, то функция возвращает TRUE. С помощью этой формулы можно, например, управлять отображением итогов.
Синтаксис формулы:

Пример: посчитать среднюю цену по товарам, а по группам цену не показывать.

15. SELECTEDVALUE

Функция SELECTEDVALUE возвращает отфильтрованное значение. Если столбец отфильтрован по одному отдельному значению, то его можно получить с помощью SELECTEDVALUE, в противном случае функция возвращает «альтернативный результат».
С помощью этой формулы можно создавать комментарии или делать вычисления, которые зависят от выделенного (отфильтрованного) значения.
Записывается формула просто:

SELECTEDVALUE ( <столбец> [, <альтернативный результат> ] )

Более интересные комментарии и заголовки можно создавать с помощью сочетаний SELECTEDVALUE и HASONEVALUE.

16. HASONEVALUE

HASONEVALUE проверяет, содержит ли контекст столбца только одно значение (или контекст должен быть отфильтрован до одного значения). Если это верно, то функция возвращает значение TRUE.
Эту функцию удобно применять, если вам нужно сделать расчет только по единичным значениям. А также для написания комментариев и пояснений.
Синтаксис формулы:

HASONEVALUE ( <столбец> )

17. RELATED

Функция RELATED ищет и возвращает связанное значение из другой таблицы. Эта формула немного похожа на ВПР в «обычном» Excel. RELATED относится к функциям связи – функции этой категории используют связи между таблицами и управляют ими. Чтобы создать вычисления с помощью RELATED, между таблицами должна существовать активная связь «один ко многим».
Записывается функция просто:

Как использовать RELATED? В сочетании с другими формулами, например с SUMX.

Пример: посчитать план выручки без НДС. Данные о ставках НДС есть в отдельной таблице.

В случае, если между таблицами нет связи, вы можете воспользоваться функцией LOOKUPVALUE Эта функция так же, как RELATED, ищет и возвращает связанное значение из другой таблицы. Отличие в том, что для LOOKUPVALUE связь между таблицами создавать не требуется, а поиск можно выполнять по нескольким столбцам. Несмотря на то, что связь между таблицами не нужна, LOOKUPVALUE, как и RELATED, все-таки требует, чтобы искомые строки и их сочетания в просматриваемых столбцах были уникальными. Если это условие не выполняется, LOOKUPVALUE выдаст сообщение об ошибке.

18. USERELATIONSHIP

USERELATIONSHIP активирует связь в вычислении в соответствии существующей связью между двумя столбцами. Сама по себе отдельно эта функция не используется, ее можно применять в формулах CALCULATE, TOTALYTD и др. Функция USERELATIONSHIP, как и RELATED, относится к функциям связи. Но в отличие от RELATED, для USERELATIONSHIP вовсе не требуется, чтобы существующие связи между таблицами были активными.
Записывается формула просто:

USERELATIONSHIP ( <столбец1>, <столбец2> )

Эта формула отлично работает, если нужно показать несколько дат из разных столбцов на одной оси. Или при создании визуализаций по данным анкет. Пример, как создать вычисления с USERELATIONSHIP, смотрите в видео.

19. ADDCOLUMNS

ADDCOLUMNS добавляет вычисляемые столбцы к таблице. На выходе рассчитывается таблица с исходными и добавленными столбцами.
Синтаксис формулы:

ADDCOLUMNS ( <таблица>, <столбец>, <выражение>[, <столбец2>, < выражение2>]…)

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

SUMX ( ADDCOLUMNS ( 'данные', "показатель", <выражение>), [показатель])

Здесь функция ADDCOLUMNS создает новый столбец в таблице с названием «показатель», а SUMX суммирует его значения.

20. SUMMARIZE

SUMMARIZE – одна из очень часто используемых DAX-формул. Она возвращает таблицу для запрошенных значений, объединенных в наборы групп. Обычно в расчетах эта формула используется для создания комбинации значений вместо таблицы со всеми данными.
Синтаксис формулы:

SUMMARIZE ( <таблица>, <столбец группировки> [, <столбец группировки> ] … [, <имя>, <выражение> ] … )

Если в «промежуточную» таблицу добавляется новый столбец, то это рекомендуется делать с помощью ADDCOLUMNS: сначала создается сгруппированная таблица с помощью SUMMARIZE, а затем в нее добавляется столбец в ADDCOLUMNS.