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

• DAX функция ALL
• Пример формулы с участием DAX функции ALL
• DAX функция ALLSELECTED
• DAX функция ALLEXCEPT
• DAX функция ALLNOBLANKROW

---

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы разберем группу необычных, но очень важных функций в 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».

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

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

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

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

 

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 ('Заявки')

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

 

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

Таблица = ALL ('Заявки'[Менеджер])

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

 

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

 

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

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

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

 

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

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

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

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

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

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

Прибыль% = 
CALCULATE(
    [Прибыль];
    ALL ('Заявки')
)

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

 

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

Прибыль% = 
DIVIDE(
    // прибыль одного менеджера
    [Прибыль]; 

    // прибыль всех менеджеров
    CALCULATE( 
        [Прибыль];
        ALL ('Заявки')
    )
)

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

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

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

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

 

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

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

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

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

В общем, суть такая — удалять фильтр со столбца [Сумма] нам не нужно, а нужно удалить фильтр только со столбца [Менеджер]. Функция ALL у нас удаляет фильтры со всех столбцов всей таблицы. И вот этот момент нам и нужно исправить. То есть, нужно в параметрах ALL указать не всю таблицу «Заявки», а только столбец [Менеджеры].

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

Прибыль% = 
DIVIDE(
    // прибыль одного менеджера
    [Прибыль]; 

    // прибыль всех менеджеров
    CALCULATE( 
        [Прибыль];
        ALL ('Заявки'[Менеджер])
    )
)

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

 

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

 

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

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

 

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

ALLSELECTED () — удаляет последний наложенный уровень фильтра.

Синтаксис:

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

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

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

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

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

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

Прибыль% = 
DIVIDE(
    // прибыль одного менеджера
    [Прибыль]; 

    // прибыль всех менеджеров
    CALCULATE( 
        [Прибыль];
        ALLSELECTED ('Заявки'[Менеджер])
    )
)

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

 

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

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

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

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

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

ALL (
    [Столбец 1];
    [Столбец 2];
    [Столбец 3];
    [Столбец 4]
)

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

ALLEXCEPT (
    'Таблица';
    [Столбец 5]
)

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

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

ALL (
    [Столбец 1];
    [Столбец 2];
    [Столбец 3];
    [Столбец 4]
)

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

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

ALLEXCEPT (
    'Таблица';
    [Столбец 5]
)

от фильтров будут очищены уже 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, в данной статье все.

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

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

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

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


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

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

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

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