Роб Колли. Формулы DAX для Power Pivot

Развитие Excel, в частности, направлено на то, чтобы дать в руки продвинутых пользователей значительные возможности по обработке больших данных. Для этого, начиная с версии 2010 Microsoft добавляет элементы бизнес-аналитики (Business Intelligence, BI) непосредственно в Excel. BI реализована в Excel в виде встроенной модели данных на основе Power Pivot. А управляют данными с помощью особого набора формул DAX. Лица, принимающие решения, не будут тратить время на изучение сводных таблиц. Им нужны не просто данные, они ожидают от аналитиков гипотез и выводов. Вот почему так востребован бизнес-анализ. Освоив формулы DAX вместе с Робом Колли,[1] вы приблизитесь к цели.

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

Вспомогательные файлы можно в один клик загрузить с http://ppvt.pro/BookFiles.[2] Чтобы работать с формулами DAX нужно активировать в Excel надстройку Power Pivot (см. по ссылке раздел Активация надстройки Power Pivot). К сожалению, для ряда версий Excel надстройка Power Pivot недоступна (подробнее см. справку MS).

Содержание

Глава 1. Революция, ориентированная на пользователей

Глава 2. Роль Power Pivot в семействе продуктов Power BI

Глава 3. Знакомство с Power Pivot

Глава 4. Загрузка данных

Глава 5. Введение в вычисляемые столбцы

Глава 6. Введение в меры DAX

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

Глава 8. Функция CALCULATE()

Глава 9. Функция ALL() для снятия фильтров

Глава 10. Мышление в нескольких таблицах

Глава 11. Что вы знаете к этому моменту

Глава 12. Несвязанные таблицы

Глава 13. Введение в DAX-функцию FILTER()

Глава 14. Управление датами

Глава 15. IF(), SWITCH(), BLANK() и другие DAX-функции условий

Глава 16. SUMX() и другие X функции (итераторы)

Глава 17. Множественные таблицы данных

Глава 18. Множественные таблицы данных с различной степенью детализации

Глава 19. Борьба за производительность

Глава 20. Power Query в помощь

Глава 21. Power BI Desktop

Глава 22. Сложные связи модели данных Power Pivot

Глава 23. Прояснение понятий «контекст фильтра» и «контекст строки»

Глава 24. Нюансы функций CALCULATE() и FILTER()

Глава 25. Пользовательский календарь

Глава 26. Продвинутые вычисляемые столбцы

Глава 27. Новые функции и переменные DAX

Глава 28. Серверное/облачное размещение данных для модели DAX

Приложения. SSAS Tabular, формулы кубов, сообщения об ошибках

Глава 2. Роль Power Pivot в семействе продуктов Power BI

Power Pivot и семейство Power BI включают набор инструментов, построенных на общих движках (рис. 1). Power Pivot с движком на основе формул DAX занимает центральное место. DAX – это акроним от Data Analysis eXpressions. На самом деле я не люблю это имя. Оно не ассоциируется с расширением Excel, а обозначает что-то совершенно новое. Воспринимайте DAX как расширение формул Excel.

Power Query можно рассматривать как предварительный процессор, который очищает и форматирует данные из Интернета или из иной не очень структурированной среды. Power Query работает на М движке (языке программирования М). Начиная с Excel 2016 Microsoft уходит от имени Power Query, но не от самого движка М и механизма подготовки данных, который представлен на вкладке Данные, в группе Получить и преобразовать данные (рис. 2).

Рис. 1. Power Pivot и семейство Power BI

Рис. 2. Функции Power Query доступны в Excel 2016 и 2019 на вкладке Данные

В качестве «последней мили» семейства Power BI выступает набор инструментов визуального представления данных: сам Excel с его сводными таблицами, диаграммами и дашбордами на основе формул кубов; Power Map; Power View; дашборды Power BI. Мы рекомендуем использовать в первую очередь Excel. Power Map и Power View – нишевые продукты, к тому же нет понимания, планирует ли Microsoft их развитие внутри Excel.

В 2015 году Microsoft выпустила новую среду под названием Power BI Desktop, которая включает в себя Power Pivot и Power Query, а также совершенно новый слой визуализации под названием панели мониторинга (дашборды). Средства визуализации богаче, чем в Excel, кроме того, MS открыл платформу сторонним разработчикам.

К оглавлению

Глава 3. Знакомство с Power Pivot

Модель данных в рамках отдельного продукта под названием SQL Server Analysis Services (SSAS) развивается Microsoft с 1994 года. В 2009 г. разработчики предложили включить эту технологию в Excel. И начиная с версии 2010 это было сделано под именем Power Pivot. Почти 25-летнее наследие технологии означает, что большое число потребностей, возникших с 1994 г. были удовлетворены разработчиками. Так что мир Power Pivot практически неисчерпаем. Изучать Power Pivot желательно в правильной последовательности. Начинать лучше с простых функций, а затем переходить к более сложным (рис. 3 и 4). По аналогии с изучением Excel: вы же не начинали с формул массива!?

Рис. 3. Кривая обучения Excel: от простого к сложному

Рис. 4. Рекомендуемый порядок освоения Power Pivot

Принципы работы с Power Pivot и с обычными сводными таблицами аналогичны:

1. Получите данные из одного или нескольких источников, поместив их на листы-таблицы в окне Power Pivot.
2. Установите связи между полями различных таблиц.
3. При необходимости дополните эти данные вычисляемыми столбцами и мерами, используя традиционные для Excel или оригинальные функции (функции в Power Pivot не русифицированы).
4. Постройте сводную таблицу на основе этих данных…
5. …либо используйте функции кубов, чтобы извлечь данные с помощью формул; этот метод более гибок, и лучше подходит для визуальных отчетов (дашбордов), ориентированных на руководство.

К оглавлению

Глава 4. Загрузка данных в Power Pivot

Вспомогательный Excel-файл для этой главы

Мы не собираемся подробно объяснять работу мастера импорта (см. Марк Мур. Power Pivot). Вместо этого мы поделимся с вами тем, что мы узнали об импорте данных за время работы с Power Pivot. Откройте Excel, перейдите на вкладку Power Pivot, нажмите на кнопку Управление (или перейдите на вкладку Данные, и найдите кнопку Управление моделью данных).

Каждая таблица данных, загружаемая в Power Pivot, располагается на отдельной вкладке. Данные в Power Pivot нельзя изменять. Можно удалять или переименовывать столбцы и вкладки, добавлять вычисляемые столбцы, но нельзя изменять!

Модель данных сохраняется в том Excel файле, из которого вы запустили Power Pivot. Вы можете открыть три файла Excel, и из каждого запустить свое окно Power Pivot. Содержимое каждого окна Power Pivot сохранится в своем файле Excel.

Power Pivot может использовать данные из самых разных источников:

• из обычных листов Excel в текущей книге
• из буфера обмена; любые таблицы, даже из Word’а
• из текстовых файлов; CSV-файлов с табуляцией или запятыми в качестве разделителя
• из баз данных Access, SQL Server, Oracle, DB2, MySQL и др.
• из листов SharePoint
• из отчетов MS SQL Server Reporting Services (SSRS)
• из облачных источников, таких как Azure DataMarket и SQL Azure
• из прочих, так называемых каналов данных; например, из Интернета.

Связанные таблицы

Если ваши данные размещены на листе Excel, лучший вариант импортировать их в Power Pivot следующий. Превратите массив данных в таблицу (встаньте на одну из ячеек и нажмите Ctrl+T английское). Дайте таблице говорящее имя (рис. 5). Перейдите на вкладку Power Pivot, нажмите кнопку Добавить в модель данных. Таблица в Power Pivot будет связана с таблицей в Excel. Изменения значений, добавление строк/столбцов в Excel отразятся в Power Pivot при нажатии кнопки Обновить. Это также способ обойти ограничение, не позволяющее редактировать данные в окне Power Pivot.

Рис. 5. Дайте таблице говорящее имя

У этого способа есть и недостатки. Нельзя связать таблицу в одной книге с окном Power Pivot из другой книги Excel. Связанные таблицы нельзя автоматически обновлять на сервере Power Pivot. Их можно обновлять только на рабочем ПК. Если данных много (десятки или даже сотни тысяч строк), обновления могут выполняться очень долго. При том, что по умолчанию Power Pivot обновляется каждый раз, когда вы выходите из окна Power Pivot и открываете его. Это происходит независимо от того, были ли изменены данные в Excel. Чтобы избежать задержки при каждом возвращении в окно Power Pivot, измените этот параметр. В окне Power Pivot перейдите на вкладку Конструктор, кликните на кнопку Параметры вычисления, и выберите Режим вычисления вручную (рис. 6). Теперь вы можете обновлять данные кнопкой Обновить на вкладке Главная.

Рис. 6. Установка режима вычисления вручную

Еще один недостаток присущ не только связанным таблицам, а всем источникам данных: какой бы тип источника данных ни использовался для создания таблицы в Power Pivot, впоследствии изменить источник данных нельзя. Необходимо удалить таблицу и повторно создать ее из нового источника.

Чтобы решить проблему больших данных, сначала сохраните лист Excel в формате CSV (значения, разделенные запятыми), а затем импортируйте этот CSV-файл в Power Pivot.

Вставка данных в Power Pivot

Если вы скопировали таблицу из Excel (или из другого источника) в буфер обмена и перешли в Power Pivot, становится активной кнопка Вставить на вкладке Главная (рис. 7).

Рис. 7. Вставка данных в Power Pivot из буфера обмена

У этого метода есть ряд преимуществ. Вы можете вставить данные из различных источников, а не только из Excel (в отличие от связанных таблиц). Можно вставить данные из других книг Excel. Когда вы создали таблицу в Power Pivot на основе данных из буфера, вы можете их заменить или дополнить новыми данными из буфера (рис. 8).

Рис. 8. Последующее изменение данных в таблице, созданной из буфера обмена

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

Импорт из текстовых файлов

На вкладке Главная кликните кнопку Из других источников, и в Мастере импорта таблиц перейдите к самой нижней строчке (рис. 9).

Рис. 9. Импорт из текстового файла

Преимущества. Почти неограниченный объем данных. Иногда вы можете добавить новые столбцы. Связанные текстовые файлы могут быть расположены в любом месте на ПК или на сетевых дисках, и Power Pivot может подключаться к ним напрямую. Так, если какой-то процесс обновляет данные каждую ночь, формируя новый файл в том же месте с тем же именем, вам достаточно нажать кнопку Обновить в Power Pivot. Если используется Power Query для подключения к таблице на веб-сайте, то данные в Power Pivot обновляются аналогично. Можно переключиться на другой текстовый файл (но не на совершенно другой тип источника).

Ограничения. Имена столбцов не являются определяющими. Если порядок столбцов в CSV-файле изменится, это, скорее всего, приведет к путанице в Power Pivot при следующем обновлении.

Импорт из базы данных

Преимущества. Почти неограниченные объемы данных. Можно добавить новые столбцы. Можно переключить на другой сервер, базу данных, таблицу, представление или запрос (но не на другой тип источника данных). Базы данных – отличное место для добавления вычисляемых столбцов. Построение вычисляемых столбцов в базе данных, а затем их импорт, а не запись вычисляемых столбцов в самой Power Pivot имеет ряд существенных преимуществ. Это особенно верно, когда ваши таблицы довольно большие.

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

Иные источники

Мы поклонники облачного хранилища данных Azure DataMarket (платного), который улучшается с каждым днем. Хотите сопоставить данные о продажах с историческими данными о погоде для каждого магазина за последние три года? Или вас интересуют обменные курсы? Может быть, исторические цены на газ? Цены на акции? Эти, а также тысячи иных данных доступны на DataMarket.

Другие советы

Имена таблиц и столбцов используются в формулах. Ранние версии Power Pivot (в отличие от Excel) не подставят новые имена в формулы. Озаботьтесь названиями до написания формул, а еще лучше – до импорта данных в Power Pivot. Начиная с Excel 2016 переименования автоматически обрабатываются в модели данных. Однако, если вы создали отчеты, связанные с вашей моделью, они могут не отработать переименование.

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

Рис. 10. Свойства связанной таблицы

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

Также полезна кнопка Существующие соединения на вкладке Главная. При нажатии на эту кнопку появится список всех подключений, ранее установленных в текущей книге (рис. 11). Кнопка Изменить позволяет изменить существующие подключения. На скриншоте выше вы видите путь к базе данных Access. Если мы хотим указать на другую базу данных Access, нажмите кнопку Изменить. Кнопка Открыть позволяет быстро импортировать новую таблицу из существующего соединения. Мы настоятельно рекомендуем сделать это, а не начинать с кнопки Из базы данных на вкладке Главная. Таким образом можно пропустить первые несколько экранов мастера импорта и не засорять книгу несколькими подключениями, указывающими на один и тот же источник.

Рис. 11. Существующие соединения

К оглавлению

Глава 5. Введение в вычисляемые столбцы

Формулы DAX можно писать в вычисляемых столбцах и в мерах. Вычисляемые столбцы проще, поэтому начнем с них. Невозможно добавить вычисляемые столбцы, пока не будут загружены данные. Откройте файл ch05_IntroCalcColumn.xlsx, перейдите на вкладку Power Pivot, кликните кнопку Управление. В открывшемся окне Power Pivot вы увидите три вкладки/таблицы (рис. 12).

Рис. 12. Три таблицы в Power Pivot; активна таблица Sales

Выберите любую ячейку в области Добавление столбца и в строке формул наберите знак =. Можно использовать привычный по Excel метод написания формул: щелкните мышью на любой ячейке в столбце SalesAmount. В строке формул появится =Sales[SalesAmount] (имя таблицы и имя поля). Второй метод заключается в продолжении набора в строке формул =[. Появится список доступных полей (рис. 13). Чтобы дополнительно ограничить список полей, можно продолжить набор =[s. Когда вы находитесь внутри списка между его строками можно перемещаться с помощью стрелок вверх и вниз. Выбор поля осуществляется нажатием клавиши <Tab>. Выберите SalesAmount.

Рис. 13. Доступные поля для использования в формуле DAX; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Введите формулу =[SalesAmount]-[ProductCost]. Нажмите Ввод. Обратите внимание, что фон вычисляемого столбца более темный. Такое оформление позволяет распознавать столбцы, которые вычисляются, а не импортируются. Также заметьте, что формулы в Excel отображаются в каждой ячейке, а в Power Pivot только в строке формул.

Переименуйте новый столбец. Щелкните правой кнопкой мыши на заголовке и выберите Переименовать. Введите название Margin. В Power Pivot имена столбцов – это не только метки. Они также выполняют роль именованных диапазонов. А не так как в Excel, где одно имя для отображения, и другое – для ссылки. Это позволяет не тратить дополнительное время на поддержание отдельных именованных диапазонов.

Создайте еще один вычисляемый столбец, который будет ссылаться, как на вычисляемый столбец, так и на импортированный: =[Margin]/[SalesAmount]. Всё аналогично!

Свойства вычисляемых столбцов

Каждая строка вычисляемого столбца использует одну и ту же формулу. Поэтому, если вы хотите, чтобы формулы в строках отличались, используйте IF(). Power Pivot всегда использует именованные ссылки. Нет ссылок в стиле A1. Ссылки на столбцы имеют вид типа [ColumnName]. Имена могут содержать пробелы. На столбцы также можно ссылаться через ‘Table Name'[ColumnName]. Для вычисляемых столбцов в одной таблице можно опустить имя таблицы. На таблицы ссылаются через ‘Table Name’. Одинарные кавычки могут быть опущены, если в имени таблицы нет пробелов. Это означает, что ссылка TableName[ColumnName] также является допустимой.

Значение вычисляемого столбца статически сохраняются в модели данных Power Pivot и в файле Excel, из которого модель была открыта. Это снижает производительность. Термин статический означает, что в ячейке хранится значение. Пересчет вычисляемого столбца инициируется только двумя событиями:

• в момент ввода или редактирования формулы для вычисляемого столбца;
• при обновлении импортированных в Power Pivot данных.

Вычисляемые столбцы являются статическими в отличие от мер, являющихся динамическими.

Более продвинутые вычисления

Имена функций также появляются в виде подсказок при наборе и с ними также возможно автозавершение. Создайте третий вычисляемый столбец; начнем вводить =SU… (рис. 14).

Рис. 14. Имена функций и автозавершение

Обратите внимание, как SUM() применяется ко всему столбцу [ProductCost], а не только к текущей строке. Привыкайте к таким функциям агрегации, как SUM(), AVERAGE(), COUNT() и т.д., которые всегда применяются ко всему столбцу.

Рис. 15. Формула SUM() суммирует весь столбец

В Power Pivot довольно много традиционных функций Excel. Например, =Month([OrderDate]), =YEAR([OrderDate]). Для быстрого доступа к функциям Power Pivot кликните на кнопку fx. (рис. 16). Если в Power Pivot вы видите знакомую по Excel функцию, знайте, она будет иметь те же параметры и поведение.

Рис. 16. Power Pivot также имеет диалоговое окно Вставка функции

К оглавлению

Глава 6. Введение в меры DAX

Мера – формула, добавляемая в сводную таблицу. Добавить меру можно двумя способами: в Excel (внутри сводной), в окне Power Pivot (в области вычислений, так же называемой сеткой мер). Рекомендуем начать с первого варианта, потому что это дает вам отличный контекст для проверки правильности формулы. Обратите внимание, что, даже при добавлении меры в Excel она по-прежнему создается в модели данных Power Pivot. Это можно проверить, перейдя в окно Power Pivot.

Создание сводной таблицы

Давайте создадим сводную, подключившись к нашей модели данных Power Pivot. Перейдите в Excel на новый лист, на вкладке Вставка кликните на кнопку Сводная таблица (рис. 17). В открывшемся окне Создание сводной таблицы обратите внимание, что по умолчанию выбрана опция Использовать модель данных этой книги. Нажмите Ok.

Рис. 17. Создание сводной на основе модели данных

Обратите внимание, что список полей сводной содержит три таблицы из трех вкладок Power Pivot (рис. 18).

Рис. 18. Поля сводной таблицы

В Excel 2016 щелкните правой кнопкой мыши на таблице Sales в области Поля сводной таблицы, выберите Добавить меру (рис. 19).

Рис. 19. Добавьте меру, кликнув правой кнопкой мыши на соответствующую таблицу

Откроется диалоговое окно Мера, которое мы будем называть редактором меры или просто редактором (рис. 20). Переименуйте меру и введите формулу =SUM(Sales[SalesAmount]). Нажмите Ok.

Рис. 20. Редактор меры

В области Поля сводной таблицы в таблице Sales появится мера Total Sales. Перенесите ее в область значений и в сводной таблице отразится суммарный объем продаж (рис. 21).

Рис. 21. В сводной таблице суммарный объем продаж отражается с помощью меры

Сводная таблица работает так, как вы и ожидаете. Перетащите номера месяцев в область Строки, а годы в область Столбцы

Рис. 22. Сводная таблица, представляющая данные об объеме продаж по годам и месяцам

Созданная мера Total Sales аналогична числовому столбцу SalesAmount. Если последний поместить в область Значения (вместо Total Sales), сводная таблица будет выглядеть также, как на рис. 22. Т.е., существует два способа подсчитать сумму в сводной: (1) написать формулу с помощью редактора мер; (2) поместить числовой столбец в область Значения. Мы будем называть явной – меру, созданную на основе формулы в редакторе; неявной – меру, «зашитую» в числовом столбце. Мнения о том, какой метод лучше, расходятся. Мы считаем, что неявные меры следует использовать в Excel. А в Power Pivot мы всегда запускаем редактор, пишем формулу и даем мере разумное имя. Явный подход дает слишком много преимуществ.

Одна из причин, почему мы предпочитаем явные меры – возможность ссылаться из формулы на другую явную меру. Создадим еще одну простую меру Profit (прибыль), рассчитываемую по формуле =SUM(Sales[Margin]). А затем меру Profit Pst (маржинальность). Начните набирать формулу =[ Появится подсказка для автозавершения (рис.23). Обратите внимание, что в списке есть и ранее созданные меры, помеченные значком fx. Напишите формулу =[Profit]/[Total Sales].

Рис. 23. Автозавершение в окне редактора Мера

Поместите в сводную таблицу Excel только меру Profit Pst. Изменения в «родительской» мере отражаются в «дочерней». Например, кликните правой кнопкой мыши на мере Profit. Выберите Редактировать меру, и в открывшемся окне Мера замените формулу на: =SUM(Sales[Margin])*1,1. Изменятся и значения Profit, и Profit Pst.

Представьте, что у вас есть набор сложных отчетов Excel, которые используют определенный метод расчета прибыли, объема продаж и т.п. И затем что-то меняется. В Excel вам, возможно, понадобятся дни, а то и недели для исправления. При правильном использовании Power Pivot изменения возможны в течение несколько секунд. Чтобы воспользоваться преимуществами, используйте имена мер в формулах, а не повторяйте формулы.

Например, эти две формулы для Profit Pct возвращают одинаковые результаты:

=SUM(Sales[Margin])/SUM(Sales[SalesAmount])

=[Profit]/[Total Sales]

Но только второй подход позволяет исправить формулы один раз. Например, если вам нужна мера продаж, которая вычисляется по-другому, правильный подход заключается в том, чтобы определить вторую меру продаж с соответствующим именем, например, [Sales No Tax] или [Sales Incl Commissions] и т.д.

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

Рис. 24. Форматирование меры позволяет избежать последующего форматирования в сводных таблицах

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

[Transactions] =COUNTROWS(Sales) и [Days Selling] =DISTINCTCOUNT(Sales[OrderDate])

Синтаксис [Name] =<формула>, это означает, что мы создаем новую меру с именем [Name] с этой формулой. Функция COUNTROWS подсчитывает количество строк в таблице, а DISTINCTCOUNT – количество уникальных значений в столбце.

Определим еще две меры, основанные на двух вышеприведенных мерах.

[Sales per Transaction] =[Total Sales]/[Transactions] и [Sales per Day] =[Total Sales]/[Days Selling]

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

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

К оглавлению
Следующая глава – Золотые правила мер DAX

[1] Роб Колли 14 лет проработал в Microsoft и был одним из инженеров-разработчиков Power Pivot. В 2010 г. Роб основал консалтинговую компанию, которая помогает осваивать BI широкому кругу пользователей. В 2013 г. вышла его книга DAX Formulas for PowerPivot. В 2016 г. вторая редакция вышла в соавторстве с Ави Сингхом под названием Power Pivot and Power BI. Здесь представлен ее сокращенный перевод. Все скриншоты выполнены в Excel Pro Plus 2019, русская версия. – Здесь и далее прим. Багузина.

[2] Я скачал их в папку C:/Common/DAX