Dax ошибка деления на ноль

 Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

• DAX функция DIVIDE
• DAX функция QUOTIENT
• DAX функция MOD

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы поговорим о том, как в Power BI (Power Pivot) защититься от ошибки деления на 0. А также, о том, как из общей суммы секунд вычислить соответствующее число часов, минут и секунд? А если говорить конкретнее, то разберем функции деления в DAX: DIVIDE (деление на ноль), QUOTIENT (целочисленное деление) и MOD (остаток от целочисленного деления).

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

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

DAX функция DIVIDE в Power BI и Power Pivot

DIVIDE () — производит деление с обработкой ошибки «деление на 0». Обработка ошибки заключается в выводе альтернативного результата в случае возникновения ситуации деления на ноль.

Синтаксис:

DIVIDE (Делимое Число; Делитель; Альтернатива)

Где, альтернатива — (необязательный параметр) значение, которое нужно вывести в случае ошибки деления на ноль (0). По умолчанию выводится пустое значение BLANK ().

Пример формулы на основе DAX функции DIVIDE.

В Power BI Desktop имеется исходная таблица «Реклама», содержащая по каждой дате затраты на рекламу и прибыль, полученную от продаж с этой рекламы:

Задача — создать в Power BI меру расчета коэффициента ROI (окупаемости затрат на рекламу). Данный коэффициент рассчитывается как сумма всей прибыли деленная на сумму всех затрат.

Сумму значений мы можем рассчитать при помощи DAX функции SUM.

В итоге, формула расчета ROI будет такой:

ROI = SUM ('Реклама'[Прибыль]) / SUM ('Реклама'[Затраты])

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

Вроде бы, все хорошо, но, если мы вынесем эту меру в отчеты Power BI и посмотрим ROI по дням, то в визуализации в одной из строк будет отображаться непонятное слово «Бесконечность»:

А все дело в том, что у нас произошла ошибка деления на ноль — прибыль 18000 была разделена на затраты, равные 0. И Power BI вместо этой ошибки вывела значение «Бесконечность».

Для того, чтобы исправить эту ситуацию, в формуле расчета ROI вместо обычного деления нужно использовать рассматриваемую DAX функцию DIVIDE. Она позволит нам произвести деление и обработать все ошибки, возникающие при делении на о. И вместо значения «Бесконечность» вывести то значение, которое нам нужно, например, пустое значение BLANK ().

В итоге, формула расчета ROI на основе функции DIVIDE будет такая:

ROI = 
DIVIDE (
    SUM ('Реклама'[Прибыль]);
    SUM ('Реклама'[Затраты])
)

Где, в первом параметре функции DIVIDE мы указали сумму прибыли, которую нужно разделить, во втором параметре — сумму затрат, на которую делится сумма прибыли. Третий параметр указывать не стали, так как по умолчанию он равен функции BLANK (), что нам и нужно.

В результате, визуализация в Power BI теперь работает правильно:

Используйте функцию деления DIVIDE всегда, когда имеется хоть малейший потенциал значения нуля в делителе Вашей формулы.

DAX функция QUOTIENT в Power BI и Power Pivot

QUOTIENT () — выполняет деление чисел, входящих в параметры функции и возвращает целочисленную часть от деления.

Синтаксис:

QUOTIENT (Делимое Число; Делитель)

Пример формулы на основе DAX функции QUOTIENT.

В Power BI имеется исходная таблица «Общая Длительность Звонков», содержащая в себе информацию по общей сумме секунд всех разговоров менеджеров:

Требуется рассчитать это количество в целых часах.

Для этого, общее количество секунд нужно разделить на количество секунд в часе (3600). Но, в результате этого деления получится дробное число, а нам нужна только целая часть результата деления. В этой ситуации нам поможет функция QUOTIENT.

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

Длительность В Часах = 
QUOTIENT (
    SUM ('ОбщаяДлительностьЗвонков'[КоличествоСекунд]);
    3600
)

И в отчете Power BI по каждому менеджеру эта мера выведет количество целых часов, которые затратили менеджеры на звонки:

DAX функция MOD в Power BI и Power Pivot

MOD () — возвращает остаток от деления со знаком делителя.

Синтаксис:

MOD (Делимое Число; Делитель)

В качестве разбора формулы на основе DAX функции MOD продолжим рассматривать прошлый пример с расчетом длительности разговора менеджеров по телефону в целых часах на основе общей суммы в секундах.

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

И сделать это можно функцией MOD:

Остаток Секунд = 
MOD (
    SUM ('ОбщаяДлительностьЗвонков'[КоличествоСекунд]);
    3600
)

Давайте проверим эту формулу в Power BI:

Действительно, если мы рассмотрим менеджера Петров из визуализации выше, то 16784 (общее количество секунд) — 14400 секунд (4 часа * 3600) = 2384 (остаток секунд). И функция MOD нам также вывела данное значение 2384.

Теперь, это получившееся значение (остаток секунд) можно еще раз разделить функцией QUOTIENT на 60 и мы получим целое количество минут из этого остатка секунд:

Остаток В Минутах = 
QUOTIENT (
    MOD (
        SUM ('ОбщаяДлительностьЗвонков'[КоличествоСекунд]);
        3600
    );
    60
)

В Power BI вычисление этой формулы будет таким:

Давайте проверим все вычисления на примере менеджера Петрова: общее количество секунд у него 16784, а 4 часа и 39 минут, это 16740 секунд (60*(4*60+39). Все правильно 16740 входит в общее количество секунд 16784.

Теперь, осталось вычислить окончательный остаток в секундах:

ОстатокВСекундах = 
SUM ('ОбщаяДлительностьЗвонков'[КоличествоСекунд])
- [ДлительностьВЧасах] * 3600
- [ОстатокВМинутах] * 60

И в Power BI все это будет выглядеть так:

Напоследок, осталось навести некий «косметический дизайн» — совместим часы, минуты и секунды в единое значение «часы : минуты : секунды», для этого, воспользуемся оператором объединения в языке DAX — & и текстом с двоеточием («:»):

ДлительностьЗвонков = 
[ДлительностьВЧасах] & ":" & [ОстатокВМинутах] & ":" & [ОстатокВСекундах]

Итоговая визуализация, демонстрирующая совместную работу DAX функций QUOTIENT и MOD в Power BI будет такая:

Таким образом, общее количество секунд, затраченное менеджером на звонки мы превратили в соответствующее отображение в часах, минутах и секундах.

Давайте проверим все вычисления, так сказать, на калькуляторе, на примере менеджера Сидоров:

Длительность звонков = 5:9:29, то есть — 5 часов, 9 минут и 29 секунд, что равно 18569 секунд (5*3600+9*60+29). А это, в свою очередь, равно исходной сумме секунд по менеджеру Сидоров (18569 секунд).

На этом, с разбором DAX функций DIVIDE (деление на ноль «0»), QUOTIENT (целочисленное деление) и MOD (остаток от целочисленного деления) в Power BI и Power Pivot, все.

    Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

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

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

 
Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

» 2 related articles
» 2 related functions

--  DIVIDE performs safe division protecting from division by
--  zero. In case of zero denominator, it returns its third 
--  argument that defaults to a blank.
DEFINE
    MEASURE Sales[Unprotected Growth %] = 
        VAR CY = [Sales Amount]
        VAR PY = CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR( 'Date'[Date] ) )
        RETURN (CY - PY) / PY
    MEASURE Sales[Protected Growth %] = 
        VAR CY = [Sales Amount]
        VAR PY = CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR( 'Date'[Date] ) )
        RETURN DIVIDE ( CY - PY, PY, BLANK () )
EVALUATE
SUMMARIZECOLUMNS ( 
    'Date'[Calendar Year],
    "Unprotected Growth %", [Unprotected Growth %],
    "Protected Growth %",   [Protected Growth %]
)

2018-2023 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

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

Набор данных

Тренироваться мы будем на простой импортированной в PowerBI Google-таблице, в которой содержится информация о продажах интернет-магазина: приобретенные товары, сумма чека, идентификатор покупателя. В PowerBI Desktop эту таблицу назовем «Заказы».

Функция SUM в DAX

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

В этом нам поможет функция SUM с простым синтаксисом:

SUM()

Всё, что нужно для расчета выручки — создать DAX-формулу с указанием столбца «Сумма чека»: 

Выручка = SUM('Заказы'[Сумма чека])

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

Функции COUNT и DISTINCTCOUNT

Предназначение данных функций — считать количество строк, их синтаксис так же прост, как и у функции SUM:

COUNT()

DISTINCTCOUNT()

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

Заказы = COUNT('Заказы'[Товары])

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

Уникальные клиенты = DISTINCTCOUNT('Заказы'[ID Покупателя])

Функция DIVIDE

Функция DIVIDE — это замена стандартной операции деления в PowerBI. Я крайне рекомендую использовать ее в своих BI-моделях, поскольку в отличие от стандартной операции деления при использовании DIVIDE не возникает ошибки в случае деления на ноль.

Синтаксис этой функции уже немного сложнее:

DIVIDE(<Числитель>, <Знаменатель> [,<альтерн. результат>])

За значение созданной меры в случае деления на ноль отвечает параметр «альтернативный результат». Это необязательный параметр, по умолчанию он равен BLANK(), которая возвращает пустой результат.

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

Средний чек = DIVIDE([Выручка];[Заказы])]

Если вам не нужны меры «Выручка» и «Заказы», вы можете использовать функции SUM и COUNT прямо внутри функции DIVIDE:

Средний чек = DIVIDE(SUM('Заказы'[Сумма чека]);COUNT('Заказы'[Товары]))

Постоянно выскакивала проблема с делением на ноль. Как от этого избавиться красиво?
Что испробовано:
— Сначала в методе checkDivisionByZero была добавлена проверка на
textBuffer.find(‘/(((0)+(0))-((0)+(0)))’), но это оказалось как мертвому припарка, потому что тут де выскочило сообщение типа
«((a+b)/((a+b) — (a+b)) — деление на ноль.
— Тогда в метод calculateColumnCalcListColumn был добавлен код

ZeroDivider = false;
        dividerpos = strscan(str2, '/',1,999);
        divider = substr(str2,dividerpos+1,strlen(str2)-dividerpos-1);
        if (dividerpos != 0 && divider != '')
        {
            bufdevider = strfmt(buf, divider);
            if (compiler.compile(bufdevider) && (! this.checkDivisionByZero(bufdevider)))
            {
                //BP Deviation documented
                tmpAmount = runbuf(bufdevider);
            }
            else
            {
              //  info(strfmt("@SYS85024", str2, _ledgerBalColumnsDim.Column, idx_calc));
                tmpAmount = 0;
            }
        if (tmpAmount == 0)
            ZeroDivider = true;
        }       
        //if (compiler.compile(buf2) && (! this.checkDivisionByZero(buf2))) 
        if (compiler.compile(buf2) && (! this.checkDivisionByZero(buf2)) && !ZeroDivider)         
{
...

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

DAX DIVIDE Function

It returns a decimal number.

SYNTAX

DIVIDE(<numerator>, <denominator> [,<alternateresult>])

numerator  The dividend or number to divide.

denominator  The divisor or number to divide by.

alternateresult  It is an optional, returned when division by zero error occurred.

By default it returns blank().

Lets look at an example of using DIVIDE function in Power Bi.

Here we have a sample dataset of student score card.

Note that, You can see MaxMarks is 0 for studid 101 , and 104 in Subject English and Math respectively.

Which are taken purposely to see the DIVIDE function ability to handle the divide by zero error, and return alternate value if it is given else blank.

StudId           Subject               Marks Obtain   MaxMarks

Lets say, you want to calculate the percentage achieved by student in each subject.

To calculate the percentage, first you need to divide Marks obtain (dividend) in subject by max marks (divisor) then multiple by the result by 100.

Lets use the DIVIDE function DAX for the division as given below.

Subject % =

DIVIDE (

SUM ( StudentScoreCard[Marks Obtain] ),

SUM ( StudentScoreCard[MaxMarks] )

) * 100

After committing, the above DAX Subject %, Lets drag it into table visual as shown below.

If you want to replace blank value with zero, then you can modify above DAX and provide an alternate value, when divide by zero error occurred.

Subject % =

DIVIDE (

SUM ( StudentScoreCard[Marks Obtain] ),

SUM ( StudentScoreCard[MaxMarks] ) ,0

) * 100

As you can see, Blank value is replaced with an alternate value 0.

 7,337 total views,  3 views today

Мы уверены, что многие читатели нашего блога хорошо разбираются в вычислениях, которые мы приводим в демонстрационных отчетах. Однако новые пользователи Power BI зачастую не знают, что такое DAX и как его можно использовать для расчета необходимых показателей. Поэтому этой статьей мы хотим начать экскурс в основы языка DAX, и в качестве примера мы возьмем отчет по посещаемости блога, который был предоставлен нам командой Mello и который мы уже рассматривали ранее.

Итак, сначала, как полагается, немного теории.

Data Analysis Expressions, сокращенно DAX (и не спрашивайте почему именно так)) — это язык запросов для Power Pivot, Power BI Desktop и SQL Server Analysis Services (SSAS). Это некий набор функций, операторов и констант, которые можно использовать в формуле или выражении, чтобы подсчитывать и возвращать одно или несколько значений. Говоря проще, DAX помогает создавать новую информацию из данных, уже имеющихся в модели.

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

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

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

Вычисляемый столбец не менее важен и также позволяет рассчитать показатели, но подсчет производится для каждой строки таблицы отдельно и результат сохраняется в отдельное поле (новый столбец таблицы). После создания подобного вычисляемого столбца его можно использовать наравне с остальными столбцами модели. Пример использования вычисляемого столбца — создание некоего столбца с ключами (уникальными идентификаторами записей, для связей с другими таблицами).

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

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

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

Здесь и далее примем следующие обозначения:

• ‘Таблица’
• ‘Таблица'[Столбец]
• [Мера]

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

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

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

Просмотры страниц :=
SUM ( 'Просмотры страниц'[Количество просмотров] )

Давайте рассмотрим из чего состоит приведенное выше выражение. С левой стороны от знака равенства указывается название меры, в данном случае это [Просмотры страниц]. Напомним, что названия необходимо давать максимально понятные, чтобы они передавали суть того значения, которое получится в результате вычисления. Это значительно облегчит дальнейшую работу с отчетом и сэкономит массу времени на выяснения что же та или иная мера считает.

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

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

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

С правой стороны знака равенства мы видим функцию SUM, которая предназначена для суммирования всех значений в столбце, который в свою очередь передается в качестве параметра и в данном случае это ‘Просмотры страниц'[Количество просмотров]. Следует отметить, что столбец [Количество просмотров] в данном случае необходимо указывать вместе с названием таблицы ‘Просмотры страниц’, в которой он находится.

Аналогичным образом, оборачивая столбцы с числовыми значениями, мы получаем и другие меры: [Входы], [Выходы], [Длительность просмотра страницы], [Длительность сеанса] и пр.

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

Например:

Процент входов :=
DIVIDE ( [Входы]; [Просмотры страниц]; 0 )

Слева от знака равенства снова название меры (мы больше к этому не будем возвращаться), а справа функция с тремя аргументами:

DIVIDE ( Числитель; Знаменатель; Альтернативный результат )

Все просто: чтоб получить процент входов из числа просмотров, мы делим [Входы] на [Просмотры страницы], и возвращаем ноль, в случае если результат такого деления возвращает ошибку.

Стоит отметить, что [Входы] и [Просмотры страниц], это меры, в которые мы выше оборачивали данные модели.

Аналогичный синтаксис имеют такие меры отчета как [Процент выходов], [Средняя длительность просмотра страницы], [Страниц на сеанс] и пр. Все их можно найти в разделе Поля в так называемых таблицах мер, у нас в отчете их две ‘Просмотры страницы’ и ‘Сеансы’.

Рассмотрим еще одну не сложную, но полезную функцию, используемую в расчете меры:

Пользователи :=
DISTINCTCOUNT ( 'Сеансы'[Идентификатор пользователя] )

Эта мера возвращает только уникальные записи из таблицы ‘Сеансы’, а именно из столбца [Идентификатор пользователя]. Почему именно так? Потому что, один пользователь может просматривать несколько страниц и при том не один раз. В таблице в этом случае будет несколько идентификаторов пользователя (т.е. сколько просмотров — столько и идентификаторов). А нам важно узнать сколько уникальных пользователей просмотрело страницу. Поэтому мы считаем сколько уникальных идентификаторов (уникальных пользователей) есть в нашей таблице с данными.

Теперь перейдем к более сложным расчетам и рассмотрим такую меру:

Средний процент прокрутки страницы :=
AVERAGEX (
    CALCULATETABLE (
        GROUPBY (
            'События';
            'События'[Идентификатор просмотра];
            "Процент прокрутки"; MAXX (
                CURRENTGROUP ();
                VALUE ( 'События'[Ярлык события] )
            )
        );
        FILTER (
            'События';
            'События'[Категория событий] = "Max Scroll"
        )
    );
    [Процент прокрутки]
) / 100

Начнем разбираться максимально подробно.

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

Теперь разберемся, как он считается.

У нас есть нужная таблица со всеми необходимыми нам исходными данными, это таблица ‘События’, где содержится вся информация по всем событиям, произошедшим на странице. Более подробно с содержанием этой таблицы Вы можете ознакомиться в нашей статьей «Пример построения модели на данных Google Analytics».

Но таблица ‘События’ содержит также и много ненужной нам сейчас информации. Так например, в таблице собраны вместе все категории событий, в то время как нам нужна только категория Max Scroll. Можно увидеть, выбрав эту категорию, что ярлык события в этом случае будет как раз равен проценту прокрутки страницы для каждого просмотра (Идентификатор просмотра).

Осталось понять, как посчитать среднее

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

Для того чтоб вычислить средний процент прокрутки, нам нужно будет построить сводную таблицу по таблице ‘События’, в которую мы соберем все максимальные значения процента прокрутки по каждому отдельному событию:

GROUPBY (
    'События';
    'События'[Идентификатор просмотра];
    "Процент прокрутки"; MAXX (
        CURRENTGROUP ();
        VALUE ( 'События'[Ярлык события] )
    )
)

В нашем случае получается что мы из исходной таблицы ‘События’, берем уникальные значения столбца [Идентификатор просмотра], и в столбец с названием [Процент прокрутки] выводим максимальное значение столбца [Ярлык события] в заданной группе CURRENTGROUP.

Далее нам нужно эти сводные данные вывести с учетом фильтра по категории. Для этого используем функцию CALCULATETABLE, которая выведет табличное выражение GROUPBY c учетом фильтра FILTER, с помощью которой мы сможем вычленить из исходных данных только данные категории Max Scroll.

FILTER (
    'События';
    'События'[Категория событий] = "Max Scroll"
)

Результатом функции CALCULATETABLE будет вот такая таблица (справа):

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

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

Синтаксис у нее такой:

AVERAGEX ( Таблица; Выражение )

где:

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

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

Таким образом, функция-итератор AVERAGEX работает поэтапно. Сначала вычисляется выражение из второго аргумента для каждой строки таблицы, указанной в первом аргументе. Затем функция агрегирует эти значения и считает среднее по данным, получившимся по итогам расчета Выражения.

Аналогично работают и другие функции-итераторы, например SUMX — сначала рассчитывает построчно результат Выражения, а затем суммирует эти данные; а MAXX — также сначала выполняет действия над каждой строкой Таблицы из первого аргумента, в соответствии с Выражением из второго, а затем берет максимальное из значений всех строк.

Итак, итератор MAXX позволяет нам посчитать максимальный процент прокрутки по нашей «виртуальной» таблице.

Обратим внимание на то, что полученный результат мы еще делим на 100. Это связано с тем, что проценты в исходной таблице заданы не в процентном выражении (например, 44%), а просто как число — 44. Если в этом случае не делить на 100, то мы получили бы 4400%.

Разделив же на 100, наша меру будет возвращать значение в виде десятичной дроби: 0,44 и для отображения значения в %% останется только на вкладке Моделирование выбрать % формат представления числа:

Готово:

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

Рассмотрим меру, рассчитывающую кол-во новых пользователей. Выглядит она так:

Новые пользователи :=
COUNTROWS (
    FILTER (
        CALCULATETABLE (
            ADDCOLUMNS (
                VALUES ( 'Сеансы'[Идентификатор пользователя] );
                "Дата первого сеанса"; CALCULATE (
                    MIN ( 'Сеансы'[Дата] )
                )
            );
            ALL ( 'Параметры дат' )
        );
        CONTAINS (
            VALUES ( 'Параметры дат'[Дата] );
            'Параметры дат'[Дата]; [Дата первого сеанса]
        )
    )
)

Рассмотрим синтаксис снаружи-внутрь. Функция COUNTROWS считает кол-во строк в таблице, возвращаемой функцией FILTER, и отфильтрованной по заданным параметрам.

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

Как правило эта функция используется не самостоятельно, а внутри DAX выражений, и позволяет создавать промежуточные «виртуальные» таблицы, вместо обычных.

Разберем, что же в итоге получается в результате работы функции FILTER  в нашем случае:

FILTER ( Таблица; Фильтр )

Таблицу, которая подвергается фильтрации, возвращает функция:

CALCULATETABLE (
    ADDCOLUMNS (
        VALUES ( 'Сеансы'[Идентификатор пользователя] );
        "Дата первого сеанса"; CALCULATE ( MIN ( 'Сеансы'[Дата] ) )
    );
    ALL ( 'Параметры дат' )
)

С помощью функции ADDCOLUMNS мы к таблице, состоящей из одного столбца с уникальными идентификаторами пользователей, сформированной выражением VALUES ( ‘Сеансы'[Идентификатор пользователя] ), добавляем столбец под названием [Дата первого сеанса], и содержит он следующие значения:

CALCULATE ( MIN ( 'Сеансы'[Дата] ) )

минимальную (первую) дату сеанса, т.е по сути появление нового пользователя.

Затем, с помощью функции CALCULATETABLE, получаем нужную нам для дальнейших расчетов таблицу, при этом мы снимаем фильтр с помощью функции ALL ( ‘Параметры дат’ ), тем самым охватывая уже весь период времени. Это значит, что в этой функции на таблицу ‘Параметры дат’ не наложены фильтры.

А условие фильтрации полученной выше таблицы, в свою очередь выглядит так:

CONTAINS (
    VALUES ( 'Параметры дат'[Дата] );
    'Параметры дат'[Дата]; [Дата первого сеанса]
)

Это означает, что таблицу из первого аргумента функции FILTER мы фильтруем таким образом, чтоб выполнялось условие: набор уникальных дат, который возвращает функция VALUES ( ‘Параметры дат'[Дата] ) должен содержать даты, соответствующие дате первого сеанса, созданной выше. Таким образом, отбирая в справочнике дат только даты первого сеанса, мы отберем именно новых пользователей.

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

В отчете они активно используются для ABC анализа. Так, прежде всего рассчитывается показатель [Накопительный просмотр страниц].

Накопительный просмотр страниц =
CALCULATE (
    SUM ( 'Просмотры страниц'[Количество просмотров] );
    ALL ( 'Страницы' );
    'Страницы'[Просмотров страницы] >= EARLIER ( 'Страницы'[Просмотров страницы] )
)

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

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

Накопительный процент =
'Страницы'[Накопительный просмотр страниц]
    / SUM ( 'Страницы'[Просмотров страницы] )

В конце нам требуется отнести все страницы в одну из трех групп:

ABC Класс =
SWITCH (
    TRUE ();
    'Страницы'[Накопительный процент] <= 0,7; "A";
    'Страницы'[Накопительный процент] <= 0,9; "B";
    "C"
)

Выражение основано на одной функции SWITCH, которая по сути является «переключателем» и предназначена для возвращения определенного значения в зависимости условия которое выполнилось.

SWITCH ( Выражение; Значение; Результат )

Первым параметром может выступать любое выражение на DAX, результатом вычисления которого является единственное значение, в нашем случае это TRUE(), которое возвращает логическое значение истина. В качестве же значений, которые используются для выбора возвращаемого результата у нас используются сравнения, результаты выполнения которых сопоставляются с TRUE(). Так, мы каждую строку столбца ‘Страницы'[Накопительный процент] проверяем на удовлетворение одному из двух условий <=0,7 и <=0,9, и если накопительный % меньше или равен 0,7 (70%), то для заданной строки будет проставлена категория А, если меньше или равен 0,9 (90%) — то категория В, а если данные в строке не удовлетворяют ни одному из условий, то категория будет С.

На этом закончим наше первое (и при этом довольно масштабное) знакомство с DAX и оставим материал для других статей на эту тему.

К чему же мы пришли? А пришли мы к выводу о том, что DAX является неотъемлемой частью Power BI и правильное понимание мер и вычисляемых столбцов ощутимо помогает строить качественную и быструю отчетность. Мы разобрали отличия между мерами и вычисляемыми столбцами, и рассмотрели практические примеры использования популярных функций, разобрали синтаксис построения формул и ознакомились с нюансами таких мер, как итераторы.

Также, мы затронули такую интересную тему, как АВС анализ. И кто знает, может скоро мы даже посвятим ему отдельную небольшую статью 🙂