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

• Подробное ВИДЕО «Вычисляем на языке DAX в Power BI и Power Pivot дату первой продажи товара»
• Расчет даты первой продажи через вычисляемый столбец
• Расчет даты первой продажи через меру
• Корректировка меры (удаление значений в строке итогов для столбца с датами)

---

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы с вами детально разберем как в Power BI и Power Pivot на языке DAX написать код, который будет вычислять дату первой продажи товара с учетом вводимых пользовательских фильтров.

В разбираемом примере нам понадобятся следующие DAX функции: FILTER, MINX, IF, HASONEVALUE, а также переменные DAX (VAR, RETURN).

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

А также, скачайте файлы к данной статье (для Power BI)

Подробное ВИДЕО «Вычисляем на языке DAX в Power BI и Power Pivot дату первой продажи товара

Ссылки из видео:
1) [Скачивайте] Файлы к уроку для Power BI: скачать
2) [Скачивайте PDF] Справочник DAX функций для Power BI и Power Pivot на русском языке: скачать

Да, и еще один момент, в рамках распродажи до 30 апреля 2024 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 30 апреля 2024 г.): узнать подробнее

до конца распродажи осталось:

 

Статья на основе видео (текстовая расшифровка)

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

 
Когда пользовательский фильтр неактивен, то отчет показывает самые ранние (первые) даты продажи товаров вообще за весь период в таблице. Например, «Товар 1» начал продаваться 13 марта 2019 г.

Если же в фильтре выбрать какой-нибудь месяц (например, Апрель), то отчет покажет нам самые ранние продажи именно в этом месяце. Из отчета видно, что «Товар 3» начал продаваться 4 апреля, «Товар 2» – 18 апреля, а «Товар 1» в этом месяце вообще не продавался.

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

 

и создадим их заново.
 

Расчет даты первой продажи через вычисляемый столбец

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

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

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

 
Первым действием будет фильтрация строк, которые содержат наименование «Товар1» в столбце [Товар], т.к. именно это наименование содержится в строке, к которой принадлежит ячейка, рассчитываемая сейчас в вычисляемом столбце.

Далее, в отфильтрованных строках в столбце [ДатаПродаж] будет найдена самая ранняя дата.

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

Давайте реализуем всё это.

Создаем новый столбец и назовем его [ДатаПервойПродажиСтолбец].
Первое действие – фильтрация, для которой используем DAX-функцию FILTER.
Параметр 1: исходная таблица ‘ДатыПродажиТовара’;
Параметр 2: выражение фильтра. Для примера пока сравним значения столбца [Товар] со значением «Товар1»
Нажимаем ОК и в результате видим ошибку.

 
Что же произошло? Функция FILTER успешно справилась со своей работой и возвратила нам отфильтрованную таблицу. Но результат работы должен возвратиться в ячейку. А как в ячейке может поместиться таблица? Поэтому возвращается #ERROR.

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

 
Жмем ОК, ошибка ушла. Поменяем тип данных для столбца [ДатаПродажи].

 
Получаем результат

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

Но возникают два вопроса:

Вопрос 1: Как функция MINX обработала и вернула нам даты?
Функция MINX – это агрегационная функция, которая работает только с числами, но даты – это же не числа!?

Всё дело в том, что в DAX все даты хранятся в виде чисел.

Поэтому функции MINX не составило труда из всех дат вычислить самую раннюю дату (как самое минимальное число)

Вопрос 2: Почему самая ранняя дата для «Товара1» возвращается и для «Товара2», и «Товара3»?

Да потому что мы договорились для примера использовать во втором параметре функции FILTER фиксированное значение «Товар1».

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

 
Чтобы это все реализовать в коде, необходимо воспользоваться переменными DAX.

Перед написанным нами ранее кодом вставляем служебное слово VAR, обозначающее начало записи кода переменной, и имя переменной «tovar» (имя пишется только на латинице!).

Дальше необходимо прописать выражение для нашей переменной. Это будет столбец [Товар] нашей таблицы. Заканчиваем запись переменной также служебным словом RETURN.

Теперь надо заменить «Товар1» на нашу переменную «tovar».

 
Жмем ОК и смотрим результат.

 
Столбец рассчитался верно.

Теперь поясню поведение DAX в таблице.

Когда DAX находится в таблице и рассчитывает какую-нибудь ячейку, то в рамках контекста строки из всей таблицы он видит только ту строку, в которой находится рассчитываемая ячейка.

Далее он погружается в код и первое, что он встречает – это переменная. Но DAX сразу переменную не рассчитывает. Он спускается ниже. Потом видит функцию MINX; далее внутрь нее; встречает функцию FILTER и доходит до таблицы ‘ДатыПродажиТовара’, указанной в первом параметре FILTER. Данную таблицу он возвращает во внутреннюю оперативную память компьютера и начинает ее фильтровать по условию, указанному в параметре 2: столбец [Товар] из таблицы ‘ДатыПродажиТовара’ должен равняться значению переменной «tovar». И вот когда DAX наткнулся на переменную, он проходит по ссылке к описанию выражения переменной.

Но данная переменная находится вне основного кода, а значит все контексты строк и фильтры, которые находятся внутри основного кода не действуют на данную переменную. На данную переменную действуют только те фильтры и контексты строк, которые находятся на уровень выше. А что у нас есть на уровень выше? Только сама таблица, в которой DAX рассчитывает ячейку. Значит переменная «tovar» будет рассчитываться под тем контекстом строки, который возвращается из исходной таблицы, а именно под действием той строки, в которой происходит расчет.

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

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

Тоже самое DAX делает для всех других строк.

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

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

Также необходимо убрать иерархию дат, так как нам нужно видеть дату, а не месяц.

 
Отчет работает и выводить первую дату продажи по всем товарам.

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

 

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

Расчет даты первой продажи через меру

На вкладке «Моделирование» создаем новую меру и называем ее «ДатаПервойПродажиМера». В данном случае нам ничего уже фильтровать не нужно, потому что сам фильтр поступит из самой визуализации. Нам останется только выбрать минимальное значение из уже отфильтрованной таблицы.
Используем также функцию MINX.

Параметр 1: исходная таблица ‘ДатыПродажиТовара’;
Параметр 2: столбец, по которому будет производиться поиск минимального значения (в нашем случае это столбец [ДатаПродажи]).

 
Нажимаем ОК и переносим нашу меру в визуализацию и зададим для меры простой формат даты (без иерархии).

 
Получаем результат. Мера наша работает.

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

 
Если же мы применим фильтр (например, нажмем «Май»), то результаты будут отличаться.

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

А почему же в мере нам ничего не нужно фильтровать?

Рассмотрим на нашем примере.

 

Когда мера находится в ячейке ①, то для этой меры уже существует фильтр
② равный «Товар1», который фильтрует таблицу, ограниченную датами месяца «Май». Далее MINX выбирает самую раннюю дату и возвращает ее в отчет.
Вот и всё!
 

Корректировка меры (удаление значений в строке итогов для столбца с датами)

Вы, наверное, уже обратили внимание на то, что в отчете рассчитывается итог по столбцу с нашей мерой.

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

Давайте сначала рассмотрим принцип формирования нашего отчета.

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

Значит меру необходимо рассчитывать только тогда, когда в столбце [Товар] будет находиться одно (единичное) значение. А если значение >1, то мера не рассчитывается.

Применим для этого конструкцию «IF — HASONEVALUE».

Исправим наш код следующим образом.

В функцию IF вставляем функцию HASONEVALUE, для которой аргументом служит столбец [Товар] исходной таблицы. Таким образом, если в столбце [Товар] будет одно значение, то HASONEVALUE возвратит TRUE и наша мера будет рассчитана. А если значений в столбце [Товар] будет больше одного, то HASONEVALUE возвратит FALSE и мера рассчитана не будет, а в отчет возвратится пустая ячейка.

 
Всё работает отлично!

На этом в этом уроке всё.

Также, напоминаю Вам, в рамках распродажи до 30 апреля 2024 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 30 апреля 2024 г.): узнать подробнее

До конца распродажи осталось:


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

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

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

до конца распродажи осталось: