Dax почему не работает формула

Формула Power BI Dax — проблема с суммой в таблице

У меня есть таблица в отчете, в которой показано количество открытых дел по возрастным группам в конце каждого месяца, при этом используются таблица возрастных групп и таблица дат, которые отделены от основной таблицы данных (в формуле ниже называется Child) . Кажется, это работает, за исключением итогов в таблице, которые ведут себя очень странно.Я искал здесь решения и вроде как понимаю, почему итоги не работают в таблицах power bi из-за контекстов, но не могу понять, почему я становлюсь таким низким цифры здесь и на чем они основаны.

Моя формула такова:

И результаты, которые я получаю в своей таблице:

Если я поставлю сумму вокруг формулы, итоги увеличатся примерно до 180, выше, но все еще слишком низко, мне не нужно иметь итоги в таблице, но я хотел бы иметь возможность понять, что происходит, чтобы увеличить мою знание формул dax

1 ответ

В общем, всегда полезно предоставить:

• небольшой набор выборочных данных
• актуальная информация о модели данных

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

Настройте DAX Studio для выполнения запросов DAX (почему позже вы узнаете, почему )

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

Теперь вы можете изменить запрос, чтобы извлечь (1) таблицу с одним столбцом «Ребенок» [идентификатор человека], которая используется для получения значения в январе и 0–4 года, и (2) таблицу с одним столбцом «Ребенок». [Идентификатор человека], который используется для получения значения за январь и итог.
Вы можете легко экспортировать эту таблицу в Excel и выяснить, в чем проблема.

Если вы заблудились, вы можете поделиться запросом, извлеченным с помощью анализатора производительности, и я могу сгенерировать вам запросы для запуска в DAX Studio.

Функции обработок ошибок в DAX: ERROR, IFERROR и ISERROR в Power BI и Power Pivot

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы рассмотрим несколько функций в Power BI и Power Pivot, которые так или иначе обрабатывают возникающие во время вычислений в DAX, ошибки. И это функции ERROR, IFERROR и ISERROR.

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

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

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

ERROR () — останавливает выполнение DAX кода и выводит заранее определенную пользователем ошибку (предупреждение).

Синтаксис: ERROR («Текст ошибки»)

Пример: в Power BI имеется исходная таблица с перечислением товаров и их количеством

Суть задачи: создать такую меру, чтобы она всегда вычисляла общее количество товара и пользователь не мог наложить никаких фильтров на это вычисление. Если пользователь накладывает фильтры, нужно остановить вычисление меры и в Power BI Desktop выдать пользователю ошибку (предупреждение).

Общее количество можно рассчитать при помощи DAX функции SUM:

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

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

Все это легко решается при помощи функций IF (условия «если») и ISFILTERED (проверяет на наличие фильтров):

Получившаяся формула вполне рабочая, если мы выберем какой-либо товар, то нам действительно выйдет предупреждение:

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

И это как раз таки можно реализовать при помощи функции ERROR, прописав внутри нее наш текст предупреждения, и вставив ERROR заместо текста предупреждения в формуле выше:

Тогда, если пользователь наложит фильтр, то DAX формула остановит свою работу:

И при нажатии на визуализации в Power BI на ссылку «См. подробности», выйдет текст самого предупреждения, который мы прописывали в ERROR:

Если убрать все фильтры, то, соответственно, формула рассчитает общее количество товаров и ни каких предупреждений от ERROR не будет.

DAX функция IFERROR (если ошибка) в Power BI и Power Pivot

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

Синтаксис: IFERROR (Выражение; Значение Если Ошибка)

В итоге возвратился результат вычисления самого выражения, так как само выражение «6 / 2» вычисляется без ошибок и равно 3.

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

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

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

ISERROR () — относится к информационным функциям DAX. Она выводит значение TRUE (Истина), если значение, входящее в ее параметр вычисляется с ошибкой, а также, значение FALSE (Ложь), если ошибок нет.

Синтаксис: ISERROR (Значение)

В первой формуле ISERROR выдала значение FALSE (Ложь), потому что выражение «6 / 2» вычисляется без ошибки. Тогда как, во втором случае выражение «6 / 0» вычисляется с ошибкой и поэтому ISERROR выдала значение TRUE (Истина).

Если ISERROR дополнить функцией условия «если» IF, то получится полный аналог DAX функции, которую мы рассматривали выше — IFERROR:

На этом, с разбором функций обработок ошибок в Power BI и Power Pivot, все.

Пожалуйста, оцените статью:

1. 5
2. 4
3. 3
4. 2
5. 1

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

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

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

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

Что еще посмотреть / почитать?

Как в Power BI (Power Pivot) получить первую (последнюю) известную дату? DAX функции FIRSTDATE и LASTDATE

DAX функции ENDOFYEAR, ENDOFQUARTER, ENDOFMONTH и STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH в Power BI и Power Pivot

Вычисление выражений на последнюю / первую даты периода в Power BI и Power Pivot: DAX функции группы CLOSINGBALANCE и OPENINGBALANCE (YEAR, QUARTER, MONTH)

Добавить комментарий

Связаться с нами: support@biprosto.ru Copyright © Проект «BI — это просто» , 2017 — 2023 ИП Будуев Антон Сергеевич. ОГРНИП 315745600033176

Оставляя персональные данные (email, имя, логин) в формах на страницах данного сайта «BI — это просто», Вы автоматически подтверждаете свое согласие на обработку своих персональных данных

Данный сайт «BI — это просто» при своей работе использует файлы cookie. Продолжая использовать сайт, Вы даете свое согласие на работу с этими файлами.

Справочник DAX функций для Power BI и Power Pivot

на русском языке с подробными примерами формул на практике

• ищете подробное описание DAX функций для Power BI или Power Pivot на русском языке
• нуждаетесь в примерах формул и их демонстрации на практике
• устали разбираться с функциями самостоятельно
• тратите огромное количество времени на создание формул методом «тыка»

то, справочник DAX функций для Power BI и Power Pivot — это то, что Вам нужно!

+ БОНУС (видеокурс по DAX)

Справочник DAX функций для Power BI и Power Pivot

на русском языке с подробными примерами формул на практике

+ БОНУС: [экспресс-видеокурс] Быстрый старт в языке формул DAX для Power BI и Power Pivot

Dax почему не работает формула

I have a fairly basic problem that doesn’t seem to rectify itself where I am trying to remove a filter context through the ALL() function, but the measure ignores this command and I do not get the expected result. From my screenshot below, I would expect the Rack Avg function to return the average of the five numbers for all rows with that date. There are no relationships to the source table and no columns sorted. Can someone explain why I may be having trouble?

Rack Measure = sum(‘Effective Rack Extracts'[Rack Price])

Rack Avg Measure = CALCULATE(AVERAGE(‘Effective Rack Extracts'[Rack Price]),all(‘Effective Rack Extracts'[Contract]))

DAX — разбор мер

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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