Язык dax для powerpivot + видео обзор

Содержание
  1. Простой авторский взгляд на сквозную BI аналитику (разбираем на практике Power BI, Excel, Power Pivot, DAX. и многое другое)
  2. Язык MS DAX для Power BI и Excel (Powerpivot)
  3. Что такое язык DAX от Microsoft?
  4. Язык DAX и Excel (Powerpivot)
  5. Язык DAX и Power BI
  6. Описание миссии языка DAX
  7. Подробное ВИДЕО «Что такое язык DAX для Power BI и Power Pivot»
  8. Выражения анализа данных (DAX) в Power Pivot
  9. Основные сведения о формулах DAX
  10. Область применения формул DAX
  11. Вычисляемые столбцы
  12. Создание формул с использованием строки формул
  13. Советы по использованию функции автозаполнения
  14. Использование нескольких функций в одной формуле
  15. Сравнение функций DAX с функциями Excel
  16. Типы данных DAX
  17. Формулы и реляционная модель
  18. Обновление результатов формул
  19. Решение проблем с формулами
  20. Совместимость с табличными моделями служб Analysis Services и режимом DirectQuery
  21. ТОП-20 DAX формул для Power Pivot и Power BI
  22. ТОП-10 DAX формул для создания мер
  23. ТОП-10 DAX формул для создания вычисляемых столбцов
  24. 5 советов по работе с Power Pivot
  25. Видео

Простой авторский взгляд на сквозную BI аналитику (разбираем на практике Power BI, Excel, Power Pivot, DAX. и многое другое)

Язык MS DAX для Power BI и Excel (Powerpivot)

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

Язык dax для powerpivotПриветствую Вас, дорогие друзья, с Вами Будуев Антон. С этой статьи я начинаю большую рубрику на нашем блоге «BI — это просто», посвященную языку функций и формул DAX. В этой рубрике будем подробно разбирать все функции DAX, а также то, как на основе них писать необходимые для Ваших BI отчетов формулы.

Ну а в этой статье мы поговорим о самом языке DAX и что это вообще такое.

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

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

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

Что такое язык DAX от Microsoft?

DAX — это язык функций и формул, который входит в состав нескольких приложений от компании Microsoft, а если быть точнее, то в приложение Power BI, в надстройку Excel (Powerpivot) и в SSAS Tabular (SQL Server).

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

В DAX Вы не сможете создать какие-либо свои функции, циклы или что-то еще, что можно создавать в языках программирования. Ведь DAX — это не язык программирования, это именно функциональный язык от Microsoft, при помощи которого, у Вас имеется возможность на основе встроенных в него функций создавать свои формулы для расчета необходимых вычислений, обогащая Вашу исходную модель данных. И на основе всего этого, в итоге, создавать те самые интерактивные отчеты в Power BI и Excel (Powerpivot).

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

Язык DAX и Excel (Powerpivot)

Тем из Вас, кто ранее пользовался обычным Экселем, а сейчас переходит к работе с языком DAX, то для Вас DAX может показаться достаточно знакомым и похожим на Excel.

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

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

И все же, DAX это не Эксель, иначе бы зачем его создавали… Большинство функций в DAX работают иначе, либо же они совершенно новые и их в стандартном Excel нет вообще.

Но, основное различие между ними даже не в тех или иных функциях, а в том, что Excel и DAX совершенно по-разному сконструированы.

В Excel вся работа основана на конкретных ячейках. К каждой ячейке Вы можете обратиться по конкретному адресу: A5, B2 и так далее.

Язык dax для powerpivot

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

Язык dax для powerpivot

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

Язык dax для powerpivot

То есть, в DAX в рамках одного столбца Вы не сможете на одной строчке написать одну формулу, на другой, другую формулу. На одной строчке один тип данных, на другой — другой тип данных. Это сделать невозможно. В DAX в одном столбце возможна лишь одна формула и один тип данных.

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

Но, все же, значение этой одной конкретной ячейки получить можно…

Язык dax для powerpivot

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

Язык DAX и Power BI

Также, есть небольшое различие в языках DAX используемых в разных приложениях, например таких, как Power BI и Excel (Powerpivot).

А конкретно, в DAX в Power BI имеется возможность создавать физически и сохранять в модели данных вычисляемые таблицы, чего в Excel (Powerpivot) по крайней мере, сейчас, пока сделать не получится. В Экселе можно создавать некие виртуальные таблицы в составе самих формул DAX, но создать физически вычисляемые таблицы и потом сохранить их в модели данных пока нельзя.

Поэтому, если Вы только начинаете изучать язык DAX, то лучше всего, по моему мнению, это делать в приложении Power BI:

Если Вы еще не знаете что такое Power BI, то на нашем блоге Вы можете найти много статей на эту тему. И если нужно скачать приложение Power BI Desktop, то сделать это Вы можете по данной ссылке.

Описание миссии языка DAX

Итак, подводя итог всей статьи про краткое описание и знакомство с DAX, можно описать его некое предназначение:

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

Подробное ВИДЕО «Что такое язык DAX для Power BI и Power Pivot»

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

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

Язык dax для powerpivot

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

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

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

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

Источник

Выражения анализа данных (DAX) в Power Pivot

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

Основные сведения о формулах DAX

Формулы DAX во многом схожи с формулами Excel. Для создания формулы DAX введите знак равенства и затем имя функции или выражения и необходимые значения или аргументы. Как и Excel, DAX поддерживает разнообразные функции, которые позволяют работать со строками, выполнять вычисления с использованием даты и времени или создавать условные значения.

Однако формулы DAX отличаются по следующим важным параметрам.

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

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

Функции аналитики времени в DAX позволяют вычисления с использованием диапазонов дат и сравнивать результаты по параллельным периодам.

Область применения формул DAX

В Power Pivot формулы можно создавать в вычисляемых с толбцах или вычисляемых п олях.

Вычисляемые столбцы

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

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

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

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

Дополнительные сведения см. в статье Вычисляемые столбцы в Power Pivot.

Мера — это формула, которая создается специально для использования в сводная диаграмма, в Power Pivot данных. Меры могут быть основаны на стандартных агрегатных функциях, таких как СЧЁТ или СУММ, или вы можете определить собственную формулу с помощью DAX. Мера используется в области значений в pivotTable. Если вы хотите разместить вычисляемые результаты в другой области pivottTable, используйте вычисляемый столбец.

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

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

Дополнительную информацию см. в статье Меры в Power Pivot.

Создание формул с использованием строки формул

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

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

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

Дополнительные сведения и пошаговые инструкции по построению формул см. в статье Создание формул для вычислений в Power Pivot.

Советы по использованию функции автозаполнения

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

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

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

Использование нескольких функций в одной формуле

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

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

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

Сравнение функций DAX с функциями Excel

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

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

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

Функции даты и времени DAX возвращают данные типа datetime. В противоположность этому функции даты и времени Excel возвращают целые числа, представляющие дату в виде порядкового номера.

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

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

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

Типы данных DAX

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

Тип данных table — это новый тип данных в DAX, которые используются в качестве входных или выходных данных для многих новых функций. Например, функция FILTER принимает в качестве входного аргумента таблицу и возвращает другую таблицу, которая содержит только строки, удовлетворяющие условиям фильтра. Объединение табличных и агрегатных функций позволяет выполнять сложные вычисления с динамически определяемыми наборами данных. Дополнительные сведения см. в разделе Агрегаты в Power Pivot.

Формулы и реляционная модель

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

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

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

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

Таблицы в Power Pivot всегда должны иметь одинаковое количество столбцов в каждой строке. Кроме того, все строки в столбце должны содержать данные одного типа.

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

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

Обновление результатов формул

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

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

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

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

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

Решение проблем с формулами

Ошибки при записи формул

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

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

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

Формула ссылается на несуществующий столбец, таблицу или функцию.

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

Формула передает функции неверное число или тип параметров.

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

Формула ссылается на необработанный столбец, т. е. она содержит метаданные, но не сами данные для вычислений.

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

Неверные или необычные результаты при ранжировании или упорядочении значений столбца

При ранжировании или упорядочении столбца, который содержит значение «не число», вы можете получить неверные или неожиданные результаты. Например, при вычислении результата деления 0 на 0 возвращается значение «не число».

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

Чтобы подтвердить правильность результатов, используйте условные операторы (с функцией ЕСЛИ) для выявления значений «не число» (с возвратом числового значения 0).

Совместимость с табличными моделями служб Analysis Services и режимом DirectQuery

В общем случае формулы DAX, создаваемые в Power Pivot, полностью совместимы с табличными моделями служб Analysis Services. Тем не менее при миграции модели Power Pivot в экземпляр служб Analysis Services и развертывании модели в режиме DirectQuery существуют некоторые ограничения.

Некоторые формулы DAX могут возвращать другие результаты при развертывании модели в режиме DirectQuery.

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

Дополнительные сведения см. в документации по табличному моделированию служб Analysis Services в электронном руководстве SQL Server 2012 BooksOnline.

Источник

ТОП-20 DAX формул для Power Pivot и Power BI

Язык dax для powerpivot

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

В этой статье мы собрали наиболее часто используемые DAX формулы для Power Pivot и Power BI и примеры файлов с ними. Какие формулы мы выбрали? Все, что доступно пользователю, перечислять смысла нет. А вот несколько формул, которые позволят решить 80% задач аналитика, рассмотреть стоит. Например, как распределить расходы. Показать остатки на конец периода, а не сумму остатков за весь период, как обычно выдается в сводной таблице. И таких примеров много.

ТОП-10 DAX формул для создания мер

1. CALCULATE

Примеры фильтров:
• отбор значений в столбце: [Столбец] = «значение» или [Столбец] > число
• отбор нужных данных в связанной таблице: FILTER ( ‘Таблица’; ‘Таблица'[Столбец] = «значение»)
• «отключить» фильтрацию данных в связанной таблице: ALL ( ‘Таблица’ ) или ALL ( ‘Таблица'[Столбец] )

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

Язык dax для powerpivot

Формулы в файле:
Уд. транспортные расходы:= DIVIDE ( SUM ( ‘транспорт'[сумма] ); CALCULATE ( SUM ( ‘продажи'[продажи] ); ALL ( ‘продажи'[наименование] ); ALL ( ‘продажи'[вид] ) ) )
Распределенные транспортные расходы:= [Уд. транспортные расходы] * SUM ( ‘продажи'[продажи] )
Прибыль после транспортных расходов:= SUM ( ‘продажи'[валовая прибыль] ) — [Распределенные транспортные расходы]


2. FILTER

Отбирает данные в таблице по указанному критерию. Результат ее вычисления — «виртуальная» таблица, которую нигде нельзя увидеть – она есть только в формуле.

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

Пример:
FILTER ( ‘Таблица’; ‘Таблица'[Столбец] = «значение» )
или FILTER ( ‘Таблица’; ‘Таблица'[Столбец] > число ).


3. ALL

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

Пример:
• удалить все фильтры из таблицы: ALL ( ‘Таблица’ )
• снять фильтры только для указанных столбцов: ALL ( ‘Таблица'[Столбец1], ‘Таблица'[Столбец2]… )

Функция ALLEXCEPT удаляет все фильтры таблицы, кроме примененных к указанным столбцам.

Пример:
• удалить все фильтры для столбцов, кроме указанных: ALLEXCEPT ( ‘Таблица’, [Столбец1], [Столбец2]…)


4. SUMX

таблицаназвание таблицы (или выражение, рассчитывающее таблицу)
столбец суммированиястолбец со значениями для суммирования (или выражение, определяющее столбец)

Пример:
• сумма произведений строк: SUMX ( ‘Таблица’; [Столбец1] * [Столбец2])
• сумма в «отфильтрованной» таблице: SUMX ( FILTER ( ‘Таблица’, ‘Таблица'[Столбец] = «значение»),’Таблица'[Столбец суммирования] )

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

Язык dax для powerpivot

Сумма в рублях:= SUMX ( ‘план’; [Платеж в валюте] * CALCULATE ( SUM ( ‘прогноз'[Прогноз курса] ) ) )


5. ADDCOLUMNS

Добавляет вычисляемые столбцы к таблице. На выходе рассчитывается таблица с исходными и добавленными столбцами.

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

Пример:
• сумма значений в таблице, созданной на основе существующей:
SUMX ( ADDCOLUMNS (‘Таблица’, «НовыйСтолбец», ), [НовыйСтолбец])
Здесь функция ADDCOLUMNS создает новый столбец в таблице, а SUMX суммирует его значения.

Язык dax для powerpivot

Формулы:
поступление:= CALCULATE ( SUM ( ‘ддс'[сумма] ); ‘ддс'[вид движения] = «поступление» )
расход:= CALCULATE ( SUM ( ‘ддс'[сумма]); ‘ддс'[вид движения] = «расход» )
денежный поток:= SUMX ( ADDCOLUMNS ( ‘ддс’; «поток» ; [поступление] — [расход] ); [поток] )


6. DATEADD

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

В примере сравнивается выручка за текущий и предыдущий год.

Язык dax для powerpivot


7. CLOSINGBALANCEYEAR

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

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

Пример:
• сумма значений на последнюю дату месяца:
CLOSINGBALANCEMONTH ( SUMX ( ‘Таблица’, ‘Таблица'[Столбец] ), ‘Таблица дат'[Столбец дат] )


8. TOTALYTD

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

В примере рассчитана сумма за квартал и за год нарастающим итогом.

Язык dax для powerpivot

Формулы:
выручка за квартал нарастающим итогом:= TOTALQTD ( [выручка]; ‘Календарь'[Date] )
выручка за год нарастающим итогом:= TOTALYTD ( [выручка]; ‘Календарь'[Date] )


9. BLANK

Функция используется для работы с «пустыми» значениями. В DAX «пустое» значение – это аналог пустой ячейки excel или NULL в базе данных. Вводится формула очень просто: BLANK ()


10. FIRSTNONBLANK и LASTNONBLANK

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

столбецстолбец (или выражение, возвращающее один столбец)
выражениевыражение, в котором проверяется наличие пустых значений в указанном столбце

В примере рассчитаны остатки ТМЦ на конец периода.

Язык dax для powerpivot

Остаток:= CALCULATE ( SUM ( ‘остатки'[Остаток ТМЦ] ); LASTNONBLANK ( ‘Календарь'[Date]; CALCULATE ( SUM ( ‘остатки'[Дата] ) ) ) )

Соответственно остатки на начало и конец периода с помощью формул LASTNONBLANK и FIRSTNONBLANK :

Язык dax для powerpivot

ТОП-10 DAX формул для создания вычисляемых столбцов

1. DIVIDE

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


2. RELATED

RELATED ( )

столбец возвращаемых значенийстолбец с нужными данными из связанной таблицы

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

Язык dax для powerpivot

Эта функция «работает» так же, как RELATED – ищет и возвращает связанное значение из другой таблицы. Отличие в том, что для работы LOOKUPVALUE связь между таблицами создавать не требуется, а поиск можно выполнять по нескольким столбцам.

столбец возвращаемых значенийстолбец с нужными данными
столбец поискастолбец, по которому выполняется поиск (из той же таблицы, что и столбец возвращаемых значений)
искомое значениестолбец искомых значений (не из таблицы поиска)


4. IF

условиевыражение, результат которого — TRUE или FALSE
значение если истинаформула или значение, если условие выполнено (TRUE)
значение если ложьформула или значение, если условие не выполнено (FALSE)

Язык dax для powerpivot

В примере клиенты разделены по группам по количеству заключенных сделок.

= IF ( [число сделок] IF ( [число сделок] > 100; «высокий»; «средний» ) )


5. SWITCH

выражениевыражение, вычисляемое для каждой строки или контекста
значениеконстанта для сравнения с результатами вычисления выражения
результатвозвращаемое значение, если совпадает с результатом вычисления
результат else(необязательно) если ни одно из не подошло, будет выбрано это значение


6. CONCATENATE

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

Аналог с использованием оператора & для большего числа строк: & & …


7. LEFT и RIGHT

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

Язык dax для powerpivot

В таблице — данные о продажах по адресам. С помощью LEFT и FIND создана таблица с группировкой по городам.

= LEFT ( [адрес отгрузки]; FIND ( «,»; [адрес отгрузки])-1 )

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


9. VALUE

VALUE ( )

тексттекст, который нужно преобразовать. Можно использовать ссылки на столбец

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


10. FORMAT

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

Пример:
День недели: = FORMAT ( [Дата]; «DDD» ) — выводит сокращенное название дня недели (пн, вт, ср, …)

Символы, которые можно использовать, чтобы задать формат дат (в формуле вводятся в кавычках)

Dдень месяца в виде числа без ведущих нулей (например, 1)
DDдень месяца в виде числа с ведущими нулями (например, 01)
DDDсокращенное название дня недели (пн, вт, ср, …)
DDDDполное название дня недели (например, воскресенье)
Mмесяц в виде числа без ведущих нулей (например, январь представляется числом 1)
ММмесяц в виде числа с ведущими нулями (например, 01/12/01)
MMMсокращенное название месяца (например, янв)
MMMMполное название месяца (например, январь)
yyгод в виде двух цифр, в случае необходимости указываются ведущие нули
yyyyгод в формате из четырех цифр

Язык dax для powerpivot

Подробнее о пользовательских форматах дат: msdn.microsoft.com
Подробнее о пользовательских числовых форматах: msdn.microsoft.com

5 советов по работе с Power Pivot

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

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

2. Используйте справочники

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

Язык dax для powerpivot

3. Скрывайте вспомогательные расчеты и таблицы

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

Чтобы скрыть таблицу, столбец или меру в представлении данных, нажмите на нее правой кнопкой мыши и выберите пункт «Скрыть из набора клиентских средств».
Язык dax для powerpivot

4. Создайте отдельную таблицу для ввода мер

В каждой таблице Power Pivot есть отдельная область для ввода мер. На самом деле, не очень удобно переходить между таблицами в поисках нужной меры – соберите их все на одном пустом листе.

В Power Pivot создать таблицу для ввода показателей можно так:
• Создайте на листе Excel пустую таблицу. Скопируйте ее.
Язык dax для powerpivot

5. Сделайте формулы читаемыми

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

• Перейдите в строку ввода формул и нажмите Alt+Enter для переноса строки.
Язык dax для powerpivot

• Кнопка Tab сдвигает текст формулы:
Язык dax для powerpivot

• С помощью символов // можно вводить комментарии:
Язык dax для powerpivot

Также комментарии можно писать в области для ввода мер.

Источник

Видео

Что такое язык DAX для Power BI и Power Pivot?

Что такое язык DAX для Power BI и Power Pivot?

Вся суть, логика и загадка языка DAX в одном видеоуроке (урок из видеокурса по DAX)

Вся суть, логика и загадка языка DAX в одном видеоуроке (урок из видеокурса по DAX)

Power Pivot Базовый №4. Как работает DAX

Power Pivot Базовый №4. Как работает DAX

Функции и формулы языка DAX в Power BI и Power Pivot. Синтаксис и правила форматирования

Функции и формулы языка DAX в Power BI и Power Pivot. Синтаксис и правила форматирования

[Трейлер] Урок 1 - Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot

[Трейлер] Урок 1 - Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot

Урок #2 по Microsoft Power BI. Примеры использования языка DAX для Power BI

Урок #2 по Microsoft Power BI. Примеры использования языка DAX для Power BI

Power Pivot.Нарастающий итог.Функция Dax

Power Pivot.Нарастающий итог.Функция Dax

[Бесплатный экспресс-курс] Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot

[Бесплатный экспресс-курс] Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot

[Трейлер] Урок 2 - Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot

[Трейлер] Урок 2 - Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot

Excel, Power Pivot, Power BI и DAX. В чем различие и что использовать? (часть урока из курса по DAX)

Excel, Power Pivot, Power BI и DAX. В чем различие и что использовать? (часть урока из курса по DAX)
Поделиться или сохранить к себе:
Добавить комментарий

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