Объединяем выбор на разных срезах в DAX: Динамический фильтр «ИЛИ»

В стандартном режиме несколько срезов в Power BI работают по принципу «И», то есть пересечения примененных фильтров. Мы выбираем на одном срезе «Яблоки», на другом цвет «Красный», и получаем меры, рассчитанные только для красных яблок. Зеленые яблоки будут проигнорированы.

Обычный срез: красные яблоки

Обычный срез: красные яблоки

Периодически (хоть и нечасто) возникает другая задача: как заставить срезы работать по принципу «ИЛИ», то есть учитывать в мере значения обоих срезов? Например, получить одновременно все зеленые фрукты и все яблоки? Все желтое или грушевидное?

Более практичный пример: товарная позиция может быть помечена в базе как складская (за это отвечает свойство «Складская»), одновременно она помечена как плановая (за это отвечает другое свойство, «Плановая»). Для расчетов нас интересуют позиции, которые могут являться, к примеру, складскими ИЛИ плановыми (то есть у них может быть установлено либо одно из этих свойств, либо оба).  Но при использовании двух обычных срезов отбор по свойству «Складская» = «Да» приведет к тому, что прочие строки будут отфильтрованы, даже если у них свойство «Плановая» тоже установлено срезом в значение «Да».

Стандартное решение

Множество интересных способов получить желаемый результат можно почерпнуть в статье гуру DAX Альберто Феррари и Марко Руссо («итальянцев»). Обычно примеры таких мер используют так называемые «прямые» фильтры – когда проводится сравнение с жестко заданным значением. Стандартное решение для таких мер следующее:

Continue Reading


Идеи усовершенствования Power Query на форумах Excel Uservoice и Power BI

Недавно я опубликовал пост о ловушке UsedRange, в которую вы можете попасть при импорте данных с листа Excel в Power Query (вы можете прочесть его тут).

Чтобы добавить возможность импорта в Power Query данных с заданных диапазонов на листе, я создал соответствующие темы на форумах excel.uservoice.com и https://ideas.powerbi.com

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

Спасибо!


Импорт данных в Power Query и Power BI из листа Excel: ловушка UsedRange

Если вы импортируете данные в Power Query или Power BI из файла Excel, обращаясь к листу целиком, будьте осторожны – вас может поджидать ловушка.

При подключении к стороннему файлу Excel нам доступны три варианта извлечения данных:

  • Таблица (форматированный как таблица диапазон ячеек на листе)
  • Именованный диапазон (диапазон ячеек, которому присвоено пользовательское имя)
  • Лист целиком

В первом случае объект «Таблица» представляет собой уже структурированные данные с заголовками столбцов, которые впоследствии автоматически трансформируются в таблицы. Во втором случае Power Query снабдит именованный диапазон автоматическими заголовками («Column1», «Column2» и так далее), и дальнейшая обработка не отличается от импортированных таблиц.

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

Данные с неразмеченного листа

К счастью, Power Query может получить данные с листа целиком. Для того чтобы получить данные из неразмеченного листа, никаких особых действий предпринимать не нужно: подключаемся к файлу, находим нужный лист (в столбце [Kind] он будет иметь значение «Sheet») и получаем данные путем обращения к его содержимому в столбце [Data]:

Листы Excel доступны в качестве источника наравне с таблицами и именованными диапазонами

Однако возникает вопрос, какие данные попадут в таблицу для этого листа? На листе Excel 17 179 869 184 ячеек (16 384 столбцов и 1 048 576 строк). Если бы Power Query пытался загрузить их все, это привело бы к безнадежным «тормозам» при импорте данных таким образом. Однако мы можем убедиться, что обычно количество строк и столбцов примерно соответствует заполненным.

Как же Power Query определяет нужный диапазон данных? Ответ может быть достаточно очевиден, если у вас есть достаточный опыт программирования на VBA и вы хорошо знакомы с объектной моделью Excel (и ответ вас не обрадует). Continue Reading


Динамический выбор мер, отображаемых на диаграммах Power BI, при помощи среза

Несколько дней назад мой клиент поинтересовался, возможно ли динамически (при помощи среза) изменить набор рядов, отображаемых на диаграмме Power BI. Мой первый (инстинктивный) ответ был «Да, конечно, вы можете использовать срез, чтобы выбрать отображаемые на диаграмме ряды, просто поместите нужный столбец в срез». Но затем клиент уточнил: выбирать на диаграмме нужно разные меры, а не разные категории из столбца. Второй (не менее инстинктивный) ответ был «Нет, это невозможно. Срез может только фильтровать столбец, и поместить меры в срез нельзя».

Однако после некоторого обсуждения я засомневался, действительно ли это невозможно. Если мы поместим меру в область значений (“Value”) диаграммы, она будет показана как ряд (например, какая-то мера [Total Amount]). А что на самом деле нужно моему клиенту? Он хочет выбрать какие-то значения на срезе и, если  некоторое значение выбрано, показать соответствующую меру. Если значение не выбрано, то не показывать эту меру.

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

Continue Reading