Как связать фильтры в power bi

Обновлено: 28.04.2024

В этой статье описаны средства моделирования данных, работающие с Power BI Desktop. Она предоставляет рекомендации по созданию двунаправленных связей модели. Двунаправленная связь — это связь, которая выполняет фильтрацию в обоих направлениях.

Общие сведения о связях в моделях в этой статье не приводятся. Если у вас есть пробелы в знаниях о связях, их свойствах или настройке, рекомендуем сначала прочитать статью Связи модели в Power BI Desktop.

Вы также должны иметь представление о проектировании схемы типа "звезда". Дополнительные сведения см. в статье Общие сведения о схеме типа "звезда" и ее значении в Power BI.

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

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

Особые связи моделей

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

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

Элементы среза "с данными"

Двунаправленные связи могут доставлять срезы, ограничивающие элементы теми, в которых есть данные. (Если вы знакомы со сводными таблицами и срезами Excel, это стандартное поведение при использовании источников данных из набора данных Power BI или модели Analysis Services.) Чтобы объяснить, что это означает, рассмотрим следующую схему модели.

Первая таблица называется Клиент и содержит три столбца: Страна или регион, Клиент и CustomerCode. Вторая таблица называется Продукт и содержит три столбца: Цвет, Продукт и номер SKU. Третья таблица называется Продажи и содержит четыре столбца: CustomerCode, OrderDate, Количествои номер SKU. Таблицы Клиент и Продукт являются таблицами типа измерения, и каждая из них имеет связь "один ко многим" с таблицей Продажи. Каждая связь выполняет фильтрацию в одном направлении.

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

Строки таблиц невозможно отобразить на схеме модели в Power BI Desktop. В этой статье это было сделано для наглядности.

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

В таблице Клиент две строки:
- CustomerCode — CUST-01, Клиент — Клиент-1, Страна или регион — США
- CustomerCode — CUST-02, Клиент — Клиент-2, Страна или регион — Австралия
- Номер SKU — CL-01, Продукт — футболки, Цвет — зеленый
- Номер SKU — CL-02, Продукт — джинсы, Цвет — голубой
- Номер SKU — AC-01, Продукт — шляпа, Цвет — голубой
- OrderDate — 1 января 2019 г. CustomerCode — CUST-01, Номер SKU — CL-01, Количество — 10
- OrderDate — 2 февраля 2019 г. CustomerCode — CUST-01, Номер SKU — CL-02, Количество — 20
- OrderDate — 3 марта 2019 г. CustomerCode — CUST-02, Номер SKU — CL-01, Количество — 30
Теперь рассмотрим следующую страницу отчета.

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

В срезе Продукт теперь отображается один элемент: футболка. Этот элемент представляет собой единственный продукт, проданный клиентам в Австралии.

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

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

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

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

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

Анализ "измерение к измерению"

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

Используя пример модели в этой статье, определите, как можно ответить на следующие вопросы:
- Сколько цветов продано клиентам в Австралии?
- Сколько стран приобрели джинсы?
На оба вопроса можно ответить без суммирования данных в таблице типа фактов с мостовым соединением. Однако они могут требовать, чтобы фильтры распространялись из одной таблицы типа измерения в другую. После распространения фильтров с помощью таблицы типа фактов формирование сводных данных столбцов таблицы типа измерения можно выполнить с помощью функции DAX DISTINCTCOUNT и, возможно, функций DAX MIN и MAX.

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

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

Рассмотрим следующее определение меры, добавленное в таблицу Продажи. В этом примере связь модели между таблицами Клиент и Продажи настроена на фильтрацию в одном направлении.

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

В следующей таблице представлены статистические данные для каждого проданного продукта. Столбец Количество — это сумма значений количества. В столбце Different Countries Sold (Продано в различных странах) представлены число разных элементов для значений "страна или регион" всех клиентов, которые приобрели этот продукт.

Дальнейшие действия

Дополнительные сведения, связанные с темой этой статьи, см. в следующих ресурсах.

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

Есть много способов фильтровать и выделять отчеты в Power BI. Включение всех этих сведений в одну статью могло привести к огромному объему информации, поэтому мы разделили их на следующие разделы:
- Общие сведения о фильтрах и выделении (статья, которую вы читаете сейчас). в службе Power BI. При этом доступные операции ограничены по сравнению с режимом правки, но вам предоставлен широкий набор возможностей фильтрации и выделения. в Power BI Desktop и службе Power BI. При наличии разрешения на правку отчета можно создавать, изменять и удалять фильтры в отчетах.
- После добавления фильтров вы сможете их форматировать, чтобы они работали нужным образом и выглядели как остальная часть отчета.
- Вы узнали, как фильтры и выделение работают по умолчанию. Теперь ознакомьтесь со сведениями о том, как изменить способ взаимной фильтрации и выделения для визуализаций.
- Узнайте о других типах фильтров в отчетах Power BI.
Знакомство с панелью "Фильтры"

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

На панели "Фильтры" можно создать четыре стандартных типа фильтров.
- Фильтр визуального элемента применяется к одному визуальному элементу на странице отчета. Фильтры уровня визуальных элементов отображаются, если выбрать визуальный элемент на холсте отчета. Даже если вы не можете изменить отчет, можно выбрать визуальный элемент и отфильтровать его.
- Фильтр страницы применяется ко всем визуальным элементам на странице отчета.
- Фильтр отчета применяется ко всем страницам в отчете.
- Фильтр детализации. Используя его в службе Power BI и Power BI Desktop, вы можете создать целевую страницу отчета, посвященную определенной сущности, например поставщику. Пользователи могут щелкнуть правой кнопкой мыши точку данных на других страницах отчета и перейти для детализации на страницу определенной сущности.
Сведения о создании первых трех типов (визуальный элемент, страница и фильтры отчетов) см. в статье Добавление фильтра в отчет в Power BI.

Сведения о создании фильтров детализации см. в статье Настройка детализации в отчетах Power BI.

Базовая и расширенная фильтрация

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

Если рядом с фильтром стоит слово All (Все), будут отображаться все значения поля без фильтрации. Например, Chain is (All) означает, что страница отчета включает данные обо всех торговых сетях. В свою очередь, фильтр уровня отчета FiscalYear is 2013 or 2014 означает, что в отчете отображаются только данные за 2013 и 2014 финансовые годы.

Расширенные фильтры позволяют использовать более сложные фильтры. Например, можно искать значения, которые содержат или не содержат определенное значение, а также начинаются или же не начинаются с него.

При создании отчета можно отключить переключение и запретить читателям отчетов изменять типы фильтров. Также можно отключить поиск на панели фильтров.

Фильтры в режиме чтения или правки

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

Фильтры в режиме чтения

Если в службе Power BI выбрать визуальный элемент в режиме чтения, панель "Фильтры" будет выглядеть так:

Каждый визуальный элемент имеет фильтры для всех своих полей. При создании отчета вы можете добавить дополнительные фильтры. На панели "Фильтры" визуальный элемент имеет три фильтра.

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

Дополнительные сведения о режиме чтения см. в статье Ознакомление с панелью "Фильтры" отчета

Фильтры в режиме правки

Если вы открываете отчет в Power BI Desktop, доступна не только панель Фильтры, но и несколько других панелей правки. Те же панели отображаются при открытии отчета в режиме правки в службе Power BI.

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

Работа с фильтрами в режиме правки

Узнайте способы добавления фильтров в отчет в Power BI Desktop и в режиме правки в службе Power BI.

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

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

Визуальные элементы отчета с перекрестной фильтрацией и перекрестным выделением

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

В этой статье описано, как добавить фильтр на уровне визуализации, страницы или отчета в отчет Power BI. Чтобы добавить фильтры, необходимо иметь права на изменение отчета. Примеры в этой статье даны для службы Power BI, действия в Power BI Desktop практически идентичны. Хотите получить более подробную информацию? Сначала узнайте о фильтрах и выделении в отчетах Power BI.

Power BI содержит множество разных видов фильтров, от ручных и автоматических до фильтров детализации и сквозных фильтров. Читать о различных видах фильтров.

После добавления фильтров вы сможете форматировать их в своих отчетах Power BI, чтобы они выглядели и работали нужным образом.

Фильтры в представлении правки или чтения

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

Так как фильтры сохраняются при выходе из отчета, в Power BI сохраняются изменения, внесенные в фильтры, срезы, и другие изменения, касающиеся представления данных. Это позволяет при повторном открытии отчета продолжить начатую работу. Чтобы изменения фильтров не сохранялись, в верхней строке меню нажмите Вернуться к значениям по умолчанию.

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

Уровни фильтров на панели "Фильтры"

При использовании Power BI Desktop или службы Power BI область "Фильтры" отображается на холсте отчетов справа. Если панель "Фильтры" не отображается, щелкните значок ">" в правом верхнем углу, чтобы развернуть его.

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

Добавление фильтра к визуальному элементу

Визуальные элементы имеют два различных вида фильтров. Вы можете добавить фильтр уровня визуального элемента к визуальному элементу двумя различными способами.
- Поля, которые находятся в визуальном элементе, автоматически фильтруются для него.
- Как создатель отчетов вы можете указать поле, которое не используется в визуальном элементе, и добавить его непосредственно в контейнер Фильтры уровня визуальных элементов.
Обратите внимание, что в этой статье используется пример "Анализ розничной торговли". Вы можете установить этот пример и ознакомиться с ним. Установите образец "Анализ розничной торговли".

Фильтрация поля, которое не принадлежит визуальному элементу

В Power BI Desktop щелкните значок отчета.

В службе Power BI выберите Изменить, чтобы открыть отчет в режиме редактирования.

Откройте панели "Визуализации", "Фильтры" и "Поля", если они еще не открыты.

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

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

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

Выберите Kids. Точечная диаграмма фильтруется, но другие визуальные элементы остаются без изменений.

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

Добавление фильтра ко всей странице

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

В Power BI Desktop откройте отчет "Анализ розничной торговли".

Щелкните значок отчета, а затем перейдите на страницу "Региональные ежемесячные продажи ".

В службе Power BI откройте отчет "Анализ розничной торговли", а затем перейдите на страницу District Monthly Sales (Продажи в регионах по месяцам).

Выберите Изменить, чтобы открыть отчет в режиме редактирования.

Откройте панели "Визуализации", "Фильтры" и "Поля", если они еще не открыты.

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

Выберите значения, которые требуется отфильтровать, и задайте элементы управления "Базовый" или "Расширенный фильтр".

Все визуальные элементы на странице перерисовываются для отражения изменений.

Добавление фильтра уровня отчета, чтобы отфильтровать весь отчет

В Power BI Desktop щелкните значок отчета.

Выберите Изменить, чтобы открыть отчет в режиме редактирования.

Откройте панели "Визуализации", "Фильтры" и "Поля", если они еще не открыты.

На панели "Поля" выберите поле, которое нужно добавить в качестве нового фильтра на уровне отчета, и перетащите его в область Фильтры уровня отчетов.

Выберите значения, которые нужно отфильтровать.

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

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

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

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

DAX функция FILTER (для Power BI и Power Pivot)

Итак, DAX функция FILTER () — возвращает таблицу, фильтруя исходную таблицу по заданным в параметрах фильтрам. Фильтрация производится по строкам, то есть, возвращаются все столбцы исходной таблицы, а строки только те, которые удовлетворяют условию фильтра.

Синтаксис: FILTER (‘Таблица’; Фильтр)
- ‘Таблица’ — исходная таблица или табличное выражение, которую необходимо отфильтровать
- Фильтр — логическое выражение, которое сравнивается с каждой строчкой таблицы, указанной в первом параметре
Обычно, данную функцию не используют в самостоятельной работе для простого создания отфильтрованной вычисляемой таблицы, хотя в Power BI это возможно.

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

Пример формулы DAX на основе работы функции FILTER

Разберем работу FILTER на простейшем примере: у нас имеется исходная таблица ‘Общие Продажи’

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

Создадим в Power BI Desktop вычисляемую таблицу, нажав кнопку «Создать таблицу» во вкладке «Моделирование». Итак, код будет таким:

Где, в качестве первого параметра мы указали исходную таблицу ‘ОбщиеПродажи’, которую нужно отфильтровать. А в качестве второго параметра само условие, по которому значение из текущей строки столбца [Отдел] в этой таблице проверяется на соответствие условию «Первый отдел».

Та строка, которая удовлетворяет этому условию, в итоге возвращается функцией FILTER, а те строки, которые не удовлетворяют — пропускаются.

Итог работы этой формулы следующий:

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

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

И, как итог, формула выдает сумму продаж только по первому отделу:

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

Функция FILTER и несколько условий фильтров

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

Вариантов здесь два:

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

Подробное ВИДЕО «DAX функция FILTER для Power BI и Power Pivot. Несколько параметров условий в FILTER»

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

«BI аналитика, и в частности составление формул на языке DAX — это действительно просто. Главное то, что описано выше в статье не просто прочитать, а повторить самостоятельно на практике, тогда Ваши результаты не заставят себя долго ждать!»

Пожалуйста, оцените статью:
1. 5
2. 4
3. 3
4. 2
5. 1
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

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

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

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

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

Функции таблиц уникальных значений в DAX: VALUES и DISTINCT в Power BI и Power Pivot

Текстовые функции в DAX: VALUE, EXACT, LOWER и UPPER в Power BI (Power Pivot)

Как в Power BI и Power Pivot сдвинуть даты? DAX функции DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR

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

Наша группа Вконтакте

Наш YouTube канал

Связаться с нами: support@biprosto.ru Copyright © Проект "BI - это просто" , 2017 - 2022 ИП Будуев Антон Сергеевич. ОГРНИП 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: ALL, ALLSELECTED, ALLEXCEPT и ALLNOBLANKROW.

Необычные они потому, что несмотря на то, что эти DAX функции в Power BI и Power Pivot относятся к категории фильтров, они работают ровно в обратном направлении, нежели фильтры.

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

Все эти 4 функции ALL, ALLSELECTED, ALLEXCEPT и ALLNOBLANKROW — удаляют ранее наложенные на таблицу фильтры, только делают они это по-разному. А как именно, разберем уже ниже, рассматривая каждую функцию отдельно.

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

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

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

ALL () — возвращает полную исходную таблицу или столбец, игнорируя все, ранее наложенные на них, фильтры. Иначе говоря, удаляет все ранее наложенные на таблицу или столбец фильтры.
- ALL (‘Таблица’) — возвращает все строки исходной таблицы
- ALL ([Столбец 1]; [Столбец 2]; …; [Столбец N]) — возвращает столбец (столбцы) со всеми уникальными значениями исходного столбца (столбцов)
Исходя из определения синтаксиса функции ALL, который описан выше, можно сказать, что ALL работает в двух разных режимах.

Когда в параметре указана просто таблица — то возвращается исходная таблица в том виде, какая она есть на самом деле.

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

Давайте эти моменты мы с Вами закрепим на практическом примере.

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

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

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

Итак, рассматриваем пример того, как функция ALL работает, когда в ее параметрах указана таблица и как она работает, когда там указан столбец.

В Power BI Desktop имеется исходная таблица «Заявки»:

Создадим на основе этой таблицы и функции ALL новую вычисляемую таблицу по следующей DAX формуле:

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

Теперь изменим наш DAX код, а именно, вместо таблицы в параметрах функции ALL укажем столбец:

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

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

Пример формулы с участием DAX функции ALL

Рассматриваем пример на основе все той же исходной таблицы «Заявки».

Во вкладке «Отчеты» в Power BI Desktop у меня уже подготовлен небольшой отчет с формулой расчета прибыли по каждому менеджеру. Для формулы расчета прибыли я использовал DAX функцию SUMX:

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

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

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

А сделать мы это сможем при помощи функций CALCULATE (про работу функции CALCULATE Вы можете прочитать в этой статье) и ALL. Где ALL, находясь в составе CALCULATE, будет удалять ранее наложенные фильтры с таблицы «Заявки». Поэтому, созданная мною ранее мера [Прибыль], также, находясь в составе CALCULATE, будет уже рассчитываться на основе талицы «Заявки», очищенной от всех фильтров.

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

Код формулы будет таким:

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

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

Для операции деления в этой формуле я использовал DAX функцию DIVIDE, которая по факту является делением (делит первый параметр на второй параметр) с обработкой ошибки деления на 0.

Результат работы этой формулы следующий:

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

Но, на самом деле, тут не все так гладко. Если в отчете добавить какой-нибудь срез, например, сделать фильтр по столбцу [Сумма] из таблицы «Заявки», то наша формула уже будет работать некорректно:

А именно, когда мы установили пользовательский фильтр «Сумма больше или равна 236», то мера [Прибыль%] — выдала нам общий результат не 100%, а 42%.

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

Соответственно, в нашем примере, ALL удалила фильтры не только по столбцу [Менеджер], но и по столбцу [Сумма].

Так как в результате фильтра по столбцу [Сумма] в таблице визуализации у нас отображаются 2 менеджера, то, соответственно, мы ожидаем расчет 100% прибыли именно по 2 менеджерам. Но, мера [Прибыль%] рассчитывает этот % исходя из всех 4 менеджеров, так как на эту меру не действует фильтр по столбцу [Сумма] — его ведь ALL удалила.

Исправим нашу формулу:

Теперь формула заработала как надо, так как ALL удаляет фильтры только со столбца [Менеджер]:

Но, на этом разбор этого практического примера еще не окончен. Давайте добавим еще один пользовательский срез, но теперь уже по самому столбцу [Менеджер]:

И у нас вновь возникла проблема. Опять общий итог равен не 100%, а 61%. Все дело в том, что функция ALL в нашей формуле удаляет все фильтры со столбца [Менеджер]. Но, нам, с одной стороны, этот фильтр нужен в созданном срезе, а с другой стороны, не нужен в таблице визуализации. Как быть?

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

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

ALLSELECTED () — удаляет последний наложенный уровень фильтра.
- ALLSELECTED () — удаляет последний наложенный уровень фильтра со всех таблиц модели данных
- ALLSELECTED (‘Таблица’) — удаляет последний наложенный уровень фильтра с указанной таблицы
- ALLSELECTED ([Столбец]) — удаляет последний наложенный уровень фильтра только с одного указанного столбца
В качестве примера формулы с использованием функции ALLSELECTED, продолжим разбирать пример, который рассматривали выше.

Итак, в нашем примере использовались несколько уровней фильтров по столбцу [Менеджер].

Первый уровень фильтра — это срез, который мы создали по менеджерам. Его нам нужно оставить (но функция ALL в примере выше его удаляла).

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

Исправим в примере нашу формулу — ALL заменим на ALLSELECTED:

Вот теперь, все точно работает правильно! DAX функция ALLSELECTED нам в этом помогла и удалила последний наложенный уровень фильтра со столбца [Менеджер] в таблице визуализации. Все % рассчитываются как надо, несмотря на то, что мы установили срезы и по менеджерам и по сумме:

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

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

Синтаксис: ALLEXCEPT (‘Таблица’; [Столбец 1]; [Столбец 2]; …; [Столбец N];)

Иногда, в Интернете я встречаю написание этой функции в 2 слова: ALL EXCEPT, что не правильно…

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

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

Эта формула удалит все фильтры из всех столбцов таблицы, кроме 5 столбца.

Но, тем не менее, между этими двумя вариантами есть разница. Если мы, в процессе работы, добавим в таблицу 6 столбец, то в варианте использования функции ALL:

от фильтров будут очищены 1, 2, 3 и 4 столбцы. 5 и 6 столбцы будут под фильтрами.

А в варианте использования функции ALLEXCEPT:

от фильтров будут очищены уже 1, 2, 3, 4 и 6 столбец. А под фильтром останется только пятый столбец.

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

ALLNOBLANKROW () — возвращает полную исходную таблицу или столбец без учета пустых строк, игнорируя все, ранее наложенные фильтры.
- ALLNOBLANKROW (‘Таблица’) — возвращает все строки исходной таблицы без учета пустых строк
- ALLNOBLANKROW ([Столбец 1]; [Столбец 2]; …; [Столбец N]) — возвращает столбец (столбцы) со всеми уникальными значениями исходного столбца (столбцов) без учета пустых строк
ALLNOBLANKROW () — функция, полностью идентичная функции ALL, за исключением того, что ALLNOBLANKROW не учитывает, автоматически создаваемые DAX, пустые строки.

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

В примере ниже, в таблице фактов «Заявки» появилась строка с заявкой от менеджера Поклонский. Но, в связанной таблице «Справочник менеджеров» данного менеджера нет. В этом случае, DAX автоматически в этой таблице создаст пустую строку:

Именно эту пустую строку ALLNOBLANKROW и не учитывает.

На этом, с разбором функций группы ALL в Power BI и Power Pivot, в данной статье все.

Пожалуйста, оцените статью:
1. 5
2. 4
3. 3
4. 2
5. 1
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

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

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

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

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

Функции времени в DAX: TIME, TIMEVALUE, HOUR, MINUTE, SECOND и NOW в Power BI и Power Pivot

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

Функции суммирования в DAX: SUMX и SUM в Power BI и Power Pivot

Читайте также: