Power BI Calculatetable vs Filter

CALCULATETABLE e FILTER são funções DAX que retornam uma tabela, ou seja, o resultado dessas funções é uma tabela. Então isso significa que essas funções, sozinhas, não podem ser utilizadas dentro de measures, mas elas podem (e são comumente utilizadas) como filtros dessas medidas.

Então, por exemplo, se você quer uma measure que te retorne a soma das vendas pelo ano de 2013, você pode escrever assim:

SalesAmount 2013 FILTER =

VAR YearFilter =

    FILTER( ALL(DATA), DATA[YEAR] = 2013 )

RETURN

    CALCULATE ( [Total Sales], YearFilter )

Perceba que a fórmula usou FILTER na tabela Data, onde o ano é 2013 e aplicou esse contexto na CALCULATE pra pegar a informação de Total Sales daquele ano. O resultado é o valor do ano 2013 se repetindo em toda tabela:

Agora, esse mesmo resultado também seria possível de se obter usando CALCULATETABLE. Assim:

Perceba que utilizar CALCULATETABLE é a mesma coisa que usar FILTER quando usamos FILTER com ALL() na tabela.

Agora, e se eu quiser que traga apenas o valor apenas para o ano, sem repetir nos outros anos o mesmo valor… Nesse caso precisamos de remover o ALL no FILTERS e no CALCULATETABLE precisamos adicionar o KEEPFILTERS. As fórmulas ficariam assim:

SalesAmount 2013 CT =

VAR YearCT  =

    CALCULATETABLE( DATA, KEEPFILTERS( DATA[YEAR] = 2013) )

RETURN

    CALCULATE ( [Total Sales], YearCT )

SalesAmount 2013 FILTER =

VAR YearFilter =

    FILTER( DATA, DATA[YEAR] = 2013 )

RETURN

    CALCULATE ( [Total Sales], YearFilter )

O resultado ficaria assim:

Fonte: https://www.learndatainsights.com/topic/write-filter-based-measures/

O motivo pelo qual desejamos usar CALCULATETABLE em vez de FILTER é porque geralmente é consideravelmente mais rápido. CALCULATETABLE é mais rápido, porque sempre funciona em colunas únicas que podem fazer melhor uso do mecanismo de armazenamento colunar por trás do Power BI; por outro lado, FILTER é uma função iteradora que calcula os resultados linha por linha para todas as colunas no escopo.

Passar em uma única coluna como o primeiro argumento para uma função FILTER, exemplo:

FILTER (ALL (Data [Data]) )

é consideravelmente mais rápido do que passar em uma tabela inteira, exemplo:

FILTER (ALL (Data))

Usando uma única coluna com FILTER é equivalente a passar os argumentos como uma condição de filtro em CALCULATE (ou usando CALCULATETABLE), embora minha preferência ainda seja usar CALCULATETABLE ou condições diretamente em CALCULATE sempre que possível.

Fonte: https://dax.guide/calculatetable/

CALCULATETABLE pode especificar apenas condições em um campo de cada vez e só pode filtrar linhas para onde esse campo se compara a um valor estático específico.

FILTER pode especificar condições em vários campos simultaneamente e também pode usar medidas como parte da condição do filtro.

Pra provar essa questão de campos, veja abaixo uma medida usando CALCULATETABLE:

YearFilter =
CALCULATETABLE (
    CalendarTable,
    CalendarTable[Year] = 2017
        || CalendarTable[Year] = 2016
)

A medida acima funciona, porque o campo YEAR está sendo comparado múltiplas vezes. Porém, se fizermos uma outra medida, usando CALCULATETABLE, assim:

CALCULATETABLE (
    CalendarTable,
    CalendarTable[Year] = 2017
        && CalendarTable[MonthNo] = 7
)

Ela não vai funcionar, porque no exemplo acima estamos usando dois campos diferentes na mesma condição e isso o CALCULATETABLE não faz.

Porém, se utilizarmos o FILTER conseguimos. Essa medida funciona, tal qual a CALCULATETABLE:

FILTER (
    CalendarTable,
    CalendarTable[Year] = 2017
        || CalendarTable[Year] = 2016
)

Porém, se quisermos adicionar campos diferentes na mesma condição, com FILTER é possível, assim:

July 2017 Sales Correct =
FILTER (
    CalendarTable,
    CalendarTable[Year] = 2017
        && CalendarTable[MonthNo] = 7
)

Sabemos que a medida Calculate considerando campos diferentes na mesma condição não funcionou, mas podemos reescrevê-la de forma que funcione, para isso precisamos separar os filtros em condições diferentes. A medida ficaria assim:

YearFilter =
CALCULATETABLE (
    CalendarTable,
    CalendarTable[Year] = 2014,
    CalendarTable[MonthNo] = 7
)

Onde FILTER se torna necessário usar sobre CALCULATETABLE é onde você específica condições mais complexas para a filtragem. Como nesse exemplo:

# Products Not sold =
VAR NotSoldFilter =
    FILTER ( ‘Product’, COUNTROWS ( RELATEDTABLE ( SalesFact ) ) = 0 )
RETURN
    CALCULATE ( COUNTROWS ( Product ), NotSoldFilter )

O CALCULATETABLE não funcionaria, daria um erro de “True/False expression does not specify a column”.

Isso acontece porque os filtros do CALCULATETABLE só podem ser do tipo boolean (true/false), tabela ou filter modification e nesse caso o filtro “COUNTROWS ( RELATEDTABLE ( FactInternetSales ) ) = 0” não retorna uma tabela e cada expressão boleana usada como expressão de filtro de tabela deve se referir exatamente a uma coluna.

Além de tudo isso, CALCULATETABLE não aceita measures como filtros, enquanto que FILTER aceita. Então, se definirmos uma measure simples, assim:

Total Sales = SUM(FactInternetSales[SalesAmount])

E entao definir a measure de Products Not Sold como:

# Products Not sold v2 =

VAR NotSoldFilter =

    FILTER ( DimProduct, [Total Sales] = 0 )

RETURN

    CALCULATE ( COUNTROWS ( DimProduct ), NotSoldFilter )

Vai funcionar, porque FILTER aceita measures nos filtros.

Portanto, para filtros mais complexos, recomenda-se o uso do FILTER, porém tenha atenção a usar FILTER por conta do CONTEXT TRANSITION. Procure saber mais sobre isso (foge do escopo desse post), mas resumindo é:

O contexto de linha aplicado por meio de FILTER é convertido em um contexto de filtro equivalente. Normalmente, os dois retornam resultados equivalentes, o que é bom; no entanto, se houver iteração de linhas não exclusivas na tabela (nesse caso, a tabela de vendas), enquanto FILTER funcionar bem em um contexto de linha pura, as linhas não exclusivas serão contadas apenas uma vez quando forem convertidas em um contexto de filtro. Por esse motivo, geralmente é mais seguro reescrever as condições do filtro diretamente, em vez de usar uma medida para aplicar os filtros.

Visão de Performance

Fonte: https://www.sqlbi.com/articles/filter-vs-calculatetable-optimization-using-cardinality-estimation/

Para entender um pouco mais sobre questão de performance, considere as duas measures abaixo:

Perceba que na primeira measure, OrdersInPlaceSingleFilter, é feito um FILTER ALL na tabela inteira de Internet Sales e então é feito um filtro composto usando && nas colunas Ship Date e Order Date.

Na segunda measure, OrdersInPlaceDoubleFilter, é feito um filtro separado, primeiro na coluna Ship Date e um segundo na coluna Order Date, e depois cada um filtra a mesma coluna.

Então, se prestar atenção, vai ver que o primeiro filtro é feito em cima de uma tabela inteira, enquanto o segundo usa apenas colunas específicas dessa tabela.

O resultado é que:

A medida OrdersInPlaceSingleFilter itera todas as 60.398 linhas nas Internet Sales. A medida OrdersInPlaceDoubleFilter executa dois filtros iterando os 1.124 valores exclusivos das colunas Order Date e Ship Date da tabela Vendas na Internet, resultando em 2.248 iterações, ou seja, muito mais rápido.

Pra entender porque isso acontece é preciso entender a CARDINALIDADE das colunas. Explicando:

• Na primeira medida, quando usando a tabela inteira você estará restrito à maior cardinalidade daquela tabela, que no caso da tabela Internet Sales deve ser a chave primária. Logo, temos 60.398 registros únicos na Internet Sales e cada um deles foi iterado usando o filtro composto que criamos.
• Já na segunda medida, por termos usados colunas diretamente, cada coluna dessa tem uma cardinalidade muito menor do que a da chave primária da tabela inteira, ou seja, no campo Order Date temos 1.124 registros únicos, essa é a nossa cardinalidade. Logo, como temos essa mesma cardinalidade também no campo Ship Date, o resultado é 1.124 nos dois filtros, resultando em 2.248 iterações na medida como um todo.

Agora, se você fizer duas medidas diferentes, uma direto na tabela e outra usando a coluna da tabela, mas com a cardinalidade igual para os dois cenários, o resultado de iterações da sua medida vai ser o mesmo, logo a performance também (a mesma ou muito parecida).

CALCULATETABLE	FILTER
Usa um campo por condição de filtro e só pode filtrar linhas para onde esse campo se compara a um valor estático específico.	Pode especificar condições de filtro em vários campos simultaneamente e também pode usar medidas como parte da condição do filtro.
Permite várias condições de filtro (<filter1>, <filter2>, …)	Permite apenas uma condição de filtro (<filter expression>)
Não aceita measures nos filtros	Aceita measures nos filtros (graças ao context transition)
Filter Context (mais rápido)	Row Context (rápido, mas menos)
Esta função não é suportada para uso no modo DirectQuery quando usada em colunas calculadas ou regras de segurança no nível de linha (RLS).	Esta função não é suportada para uso no modo DirectQuery quando usada em colunas calculadas ou regras de segurança no nível de linha (RLS).

Definições FILTER vs CALCULATE

Usar FILTER com uma tabela como parâmetro é geralmente menos performático do que usar CALCULATETABLE ou FILTER com um campo como parâmetro.

Com FILTER você pode usar measures nos filtros, enquanto que no CALCULATETABLE não.

CALCULATETABLE funciona diretamente nas colunas por seu contexto de filtro, trabalhando com uma coluna por vez a cada condição de filtro, enquanto que FILTER você pode trabalhar com várias diferentes colunas na mesma condição de filtro.

CALCULATETABLE não pode ser utilizado dentro do CALCULATE na parte da <expression>… Pode apenas ser usado na parte de filtros. Isso porque nessa parte de <expression> do CALCULATE apenas expressões que retornem valores do tipo “scalar” (single-values).

FILTER não ignora DATA TYPES dos campos de filtro, e nem CALCULATETABLE.

Mais informação:

https://www.sqlbi.com/articles/filter-vs-calculatetable-optimization-using-cardinality-estimation/