Глава 7. Золотые правила мер DAX

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава       Содержание    Следующая глава

Цель этой главы – научиться думать так, как работает механизм DAX. Например, что означает число 98 600 на рис. 7.1? Наверное, вы ответите: «Объем продаж в 2001 году артикула 344». Думая в стиле DAX, вы могли бы сказать: «Если отфильтровать таблицу Sales по строкам, в которых Year=2001 и ProductKey=344, а затем суммировать столбец SalesAmount по оставшимся строкам, то получится $98 600».

Рис. 7.1. Сводная таблица в файле ch07_GoldenRules.xlsx

Скачать заметку в формате Word или pdf

Чтобы убедиться в этом, откройте файл ch07_GoldenRules.xlsx, перейдите в окно Power Pivot на вкладку Sales, нажмите кнопку Отменить все фильтры (если кнопка неактивна, значит уже все фильтры сняты). В левом нижнем углу экрана вы увидите число строк (рис. 7.2). А в области вычислений – созданные ранее меры и их значения. Удобство области вычислений в том, что она превосходно демонстрирует логику DAX.

Рис. 7.2. На вкладке Sales при снятии всех фильтров отображается 60 398 строк

Установите фильтр на колонке Year (рис. 7.3), и посмотрите на изменения в числе строк и мерах (рис. 7.4).

Рис. 7.3. Фильтра на колонке Year

Рис. 7.4. Отобранное число строк и значения мер при фильтре Year=2001

Правила DAX выводятся из этого примера.

Правило A: меры DAX вычисляются по исходным данным, а не по данным в сводной таблице.

Возможно, вы замечали, что среднее средних или среднее частных от деления в сводной таблице показывает не те значения, на которые вы рассчитывали. Так, например, итоговое частное от деления Profit Pst = Profit/Total Sales не равно среднему по строкам (рис. 7.5).

Рис. 7.5. Среднее значение по шести выбранным ячейкам E4:E6 и Е8:Е10 составляет 45,5%, а общий итог в сводной таблице – 44,0%. Правильный результат будет получен только при расчете по отфильтрованным строкам таблицы Sales (в Power Pivot)

Правило B: каждая мера рассчитывается независимо.

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

Рис. 7.6. Вычисления на основе формул DAX в ячейках 1-4 независимы

Правило C: меры DAX вычисляются за 6 шагов.

Шаг 1. Определите набор фильтров ячейки сводной таблицы. Перед тем, как формула DAX начнет работу, она изучает фильтры текущей ячейки, для которой рассчитывается мера (рис. 7.7).

Рис. 7.7. Определение фильтров ячейки

Выбранная ячейка сводной таблицы имеет три фильтра, поступающие из строки, столбца и среза. Если вспомнить, что мера измеряется в Power Pivot, то можно сказать, что на таблицу Sales наложено три фильтра: Sales[MonthNum]=8; Sales[Year]=2001 и Sales[ProductKey]=313. Обратите внимание на формат указания фильтра ячейки: Table[Column]. Сейчас это может показаться излишним, но станет удобным, когда мы начнем работать с несколькими таблицами.

Шаг 2. Измените набор фильтров, если используете функцию CALCULATE(). Этот шаг поясним в главе 8 (мы упомянули его сейчас, чтобы при последующем изложении сохранить порядок шагов).

Шаг 3. Примените фильтры к таблице с исходными данными.

Шаг 4. Примените фильтры к таблицам подстановки; фильтры передаются по направлению связей между таблицами; это приводит к тому, что в таблице данных будут отобраны только строки, соответствующие набору всех фильтров. Этот шаг поясним в главе 10.

Шаг 5. Рассчитайте формулы. В нашем случае арифметика проста: SUM(Sales[SalesAmount]), но сложная арифметика будет выполняться аналогичным образом на отфильтрованном наборе строк. Другими словами, никакая функция не будет выполняться до тех пор, пока набор фильтров не будет применен к исходным таблицам.

Шаг 6. Верните результат в ячейку. Затем процесс начинается с шага 1 для следующей ячейки.

Важно! Когда вы ссылаетесь на столбец в формуле меры, он всегда должен быть внутри какой-то функции. «Голая» ссылка на столбец приведет к ошибке в мере. Например, создайте меру [My New Measure] = Sales[Margin], и кликните Проверить формулу DAX в окне редактора (рис. 7.8). Появится сообщение об ошибке.

Рис. 7.8. Ссылка на столбец без формулы возвращает ошибку

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

Рекомендую по-разному ссылаться на столбцы и меры:

• для ссылки на столбец включать имя таблицы: TableName[ColumnName]
• для ссылки на меру опускать имя таблицы: [MeasureName]

При этом имя таблицы, которой назначена мера, можно увидеть в редакторе мер (см. рис. 7.8).

Простое правило: мы назначаем меры таблицам, содержащим числовые столбцы, используемые в формуле. Это хороший стиль. Вашу модель легче понять (вам и другим пользователям). Если мера возвращает значения из столбца таблицы Sales, лучше назначить меру таблице Sales. Назначение меры таблице Customers может при последующем анализа заставить думать, что мера каким-то образом связана с клиентами, а не с продажами (при этом таблица, с которой связана мера, напрямую не влияет на результат, который определяется только формулой).