Особенности REMOVEFILTERS в DAX из Power BI / Хабр

Привет, Хабр! Одной из важных функций-модификаторов в DAX является REMOVEFILTERS, он позволяет, например, убрать фильтр для расчета знаменателя в доле. Однако логика REMOVEFILTERS для столбцов может выглядеть неочевидной, например, REMOVEFILTERS только для одного поля, по которому есть условие в FILTER, не влияет на результат DAX запроса. Так, REMOVEFILTERS(customer[customer_id]) не влияет на FILTER в SUMMARIZECOLUMNS вида FILTER(customer, customer[customer_id] > 2) и для сброса фильтра нужен REMOVEFILTERS(customer) по всей таблице. В связи с этим удобно представить принципы работы REMOVEFILTERS более формально, например, в виде ER диаграммы с подписанными связями. Для построения ER диаграммы был выбран Mermaid и генерация кода диаграммы реализована на C#. Интересующимся особенностями REMOVEFILTERS — добро пожаловать под кат :)

Рассмотрим простую схему данных с одной таблицей фактов sales и двумя справочниками — клиентов customer и продуктов product:

В таблице фактов sales есть 5 строк:

Рассмотрим простейший DAX запрос без фильтра, здесь CALCULATE не нужен, он добавлен только для иллюстрации будущего места применения REMOVEFILTERS, в результатах запроса видны данные по 3 продуктам:

Добавим фильтр по customer[customer_id], и останутся данные только для третьего продукта:

Теперь рассмотрим применение REMOVEFILTERS. Для иллюстрации рассмотрим вначале REMOVEFILTERS для полей, которые не меняют результат запроса и «стреляют мимо».

REMOVEFILTERS по product[product_id] не изменяет результат DAX запроса:

REMOVEFILTERS по customer[customer_id] не изменяет результат:

REMOVEFILTERS по customer[customer_name] также не изменяет результат:

Видно, что сброс фильтра в Power BI через REMOVEFILTERS по полям может быть не так очевиден. Рассмотрим теперь REMOVEFILTERS по тем полям, которые изменяют результаты DAX запроса.

REMOVEFILTERS по колонке группировкиproduct[product_name] изменяет результат запроса — теперь связь таблицы продаж sales с таблицей продуктов product теряется и в SUMMARIZECOLUMNS получаем декартово произведение всех продуктов и продаж, причем фильтр по customer сохраняется:

REMOVEFILTERS по всем столбцам customer (или просто REMOVEFILTERS(customer)) изменяет результат запроса — снимается фильтр по customer:

С учетом того, что есть удобные средства представления ER схем вроде Mermaid, удобно рассмотреть REMOVEFILTERS на основе прототипа работы со схемой данных на C#, который также и генерирует ER диаграмму на Mermaid. Представим таблицы в виде словаря с колонками tables, для упрощения не будем пользоваться ID таблиц и колонок и будем называть колонки полным именем с учетом имени таблицы. Связи между таблицами в направлении*:1 представим в виде словаря manyToOne с Tuple-ключом (string table, string column) — имя таблицы и имя колонки из таблицы many и таким же (string table, string column)хранимым значением — имя таблицы и имя колонки из таблицы one.

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

var tables = new Dictionary<string, HashSet<string>>
{
    { "sales", ["sales.order_number", "sales.customer_id", "sales.product_id", "sales.amount"] },
    { "customer", ["customer.customer_id", "customer.customer_name"] },
    { "product", ["product.product_id", "product.product_name", "product.product_color"] }
};

var manyToOne = new Dictionary<(string table, string column), (string table, string column)>
{
    {("sales", "sales.customer_id"), ("customer", "customer.customer_id")},
    {("sales", "sales.product_id"), ("product", "product.product_id")}
};

var typeByColumnName = new Dictionary<string, string>
{
    { "sales.order_number", "INTEGER" },
    { "sales.customer_id", "INTEGER" },
    { "sales.product_id", "INTEGER" },
    { "sales.amount", "DOUBLE" },
    { "customer.customer_id", "INTEGER" },
    { "customer.customer_name", "STRING" },
    { "product.product_id", "INTEGER" },
    { "product.product_name", "STRING" },
    { "product.product_color", "STRING" }
};

Далее при помощи метода ToMermaidErDiagram можно сгенерировать ER диаграмму, и это будет общая диаграмма, без учета конкретного контекста CALCULATE в рамках DAX запроса:

public static string ToMermaidErDiagram(
    Dictionary<string, HashSet<string>> tables,
    Dictionary<(string table, string column), (string table, string column)> manyToOne,
    Dictionary<string, string> typeByColumnName)
{
    var sb = new StringBuilder();
    sb.AppendLine("erDiagram");

    foreach (var (tableName, columns) in tables)
    {
        sb.AppendLine($"  {tableName} {{");

        foreach (var column in columns)
        {
            sb.AppendLine($"    {typeByColumnName.GetValueOrDefault(column, "STRING")} {column.Replace(".", "_")}");
        }

        sb.AppendLine("  }");
    }

    foreach (var (many, one) in manyToOne)
    {
        sb.AppendLine($"  {one.table} ||--o{{ {many.table} : \"{many.table}-{one.table}\"");
    }

    return sb.ToString();
}

Само получение Mermaid ER диаграммы с помощью этого метода выглядит следующим образом:

var erDiagram = ToMermaidErDiagram(tables, manyToOne, typeByColumnName);

На mermaid.live сгенерированная диаграмма выглядит так:

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

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

К примеру, такая расстановка полей группировки и агрегации SUM соответствует следующему DAX запросу:

EVALUATE
	SUMMARIZECOLUMNS(
		product[product_name],
		"result", CALCULATE(SUM(sales[amount])
		)
	)

В качестве иллюстрации, в рамках прототипа легко также можно и объединить таблицы (но DAX так делает не всегда, например, логика SUMMARIZECOLUMNS не всегда подразумевает именно такое объединение таблиц):

var joinedTables = new Dictionary<string, (List<string> columns, List<string> tables)>();
foreach (var (manyToOneTable, oneTable) in manyToOne)
{
    if (!joinedTables.TryGetValue(manyToOneTable.table, out var joinedTable))
    {
        joinedTable = new(tables[manyToOneTable.table].ToList(), [manyToOneTable.table]);
        joinedTables.Add(manyToOneTable.table, joinedTable);
    }

    foreach (var column in tables[oneTable.table])
    {
        joinedTable.columns.Add(column);
    }
    joinedTable.tables.Add(oneTable.table);
}
var erDiagram = ToMermaidErDiagram(joinedTables.Select(t => (t.Key, t.Value.columns))
    .ToDictionary(x => x.Key, x => x.columns.ToHashSet()), [], typeByColumnName);

Тогда ER диаграмма будет:

Видно, что построение диаграммы упрощается благодаря полному наименованию столбцов с указанием имени таблицы, и становится достаточно заменить точку на знак _, т.е. например, заменить sales.customer_id на sales_customer_id. Видно, что по умолчанию получаем таблицу sales со справочниками в соответствии со связями many-to-one.

Теперь можно вернуться к поведению REMOVEFILTERS. На основе рассмотренных кейсов видны две особенности REMOVEFILTERS в DAX Power BI в базовых кейсах:

при фильтрации через группировку по полю (например, в SUMMARIZECOLUMNS) использование REMOVEFILTERS по этому полю удаляет группировку и связь с этой таблицей (REMOVEFILTERS(product[product_name]), также при отсутствии связей SUMMARIZECOLUMNS для двух таблиц генерирует декартово произведение;
при фильтрации через FILTER по таблице удаление одного поля не помогает, для отмены фильтрации нужно сделать REMOVEFILTERS по всем полям таблицы.

UPDATE: при отмене фильтра по нескольким полям нужно также учитывать кейсы снижения гранулярности, т.е. при REMOVEFILTERS по нескольким полям при снижении гранулярности фильтр FILTER в SUMMARIZECOLUMNS будет частично или полностью отменяться

Таким образом, можно представить эту логику так, что в DAX есть механизм объединения таблиц в соответствии со сгруппированными полями, агрегациями, связями и т.д., и REMOVEFILTERS удаляет метаданные (связи), и из-за этого меняются результаты. Благодаря прототипу на C# и сгенерированной Mermaid диаграмме можно проиллюстрировать все формально, для этого можно взять сгенерированную ER диаграмму для трех таблиц и актуализировать названия связей:

erDiagram
  sales {
    INTEGER sales_order_number
    INTEGER sales_customer_id
    INTEGER sales_product_id
    DOUBLE sales_amount
  }
  customer {
    INTEGER customer_customer_id
    STRING customer_customer_name
  }
  product {
    INTEGER product_product_id
    STRING product_product_name
    STRING product_product_color
  }
  customer ||--o{ sales : "фильтрация по таблице customer в FILTER, поля customer[customer_id] и customer[customer_name]"
  product ||--o{ sales : "группировка по product[product_name]"

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

Такая схема в чем-то может быть формальным представлением контекста CALCULATE, которое может использоваться для REMOVEFILTERS. Эта схема для конкретного контекста, для «конкретной точки» DAX, с учетом группировки. Например, из такой схемы становится наглядно, почему при REMOVEFILTERS(customer[customer_id], customer[customer_name]) (или просто REMOVEFILTERS(customer)) меняются результаты и отменяется фильтр по customer в таком DAX:

EVALUATE
	SUMMARIZECOLUMNS(
		product[product_name],
		FILTER(
			customer,
			customer[customer_id] > 2
		),
		"result", CALCULATE(
			SUM(sales[amount]),
			REMOVEFILTERS(
				customer[customer_id],
				customer[customer_name]
			)
		)
	)

В этом случае фильтр по customer в связи между sales и customer отменяется, но сама связь между таблицами остается, в подписи для связи между sales и customer уже нет фильтра, есть только sales-customer:

При REMOVEFILTERS по полю группировкиREMOVEFILTERS(product[product_name]) связь удаляется полностью и SUMMARIZECOLUMNS делает декартово произведение product на sales с отфильтрованными результатами по customer[customer_id]:

Эта схема данных соответствует контексту CALCULATE в DAX:

EVALUATE
	SUMMARIZECOLUMNS(
		product[product_name],
		FILTER(
			customer,
			customer[customer_id] > 2
		),
		"result", CALCULATE(
			SUM(sales[amount]),
			REMOVEFILTERS(
				product[product_name]
			)
		)
	)

Соответственно, другие REMOVEFILTERS (например, REMOVEFILTERS(customer[customer_id])) «бьют мимо» и не влияют на результат, т.е. связи могут быть удалены только при REMOVEFILTERS по всем колонкам, указанным в описании связи.

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

Надеюсь, описанные особенности REMOVEFILTERS в Power BI могут быть интересны, успехов в мерах и дашбордах :)