Power BI CROSSFILTER

No exame da Microsoft o termo “cross-filter” aparece em dois tópicos:

1. define a relationship’s cardinality and cross-filter direction
2. apply cross-filter direction and security filtering

Importante você entender que cross-filter não significa necessariamente que a direção será BOTH, cross filter pode ser SINGLE também (ou pode ser NONE se estivermos falando da função DAX).

Então no tópico 1 eles estão mostrando que é preciso entender os diferentes tipos de cardinalidade de relacionamento (1 pra 1, muitos pra muitos, 1 pra muitos, muitos pra 1) e como os filtros SINGLE ou BOTH afetam esses relacionamentos. Eu já falei disso no post sobre relationships, também falei no post sobre weak relationships e no many to many relationships.

Já o tópico 2 fala sobre utilizar cross-filter e como isso se relaciona com RLS, ou “apply security filters em both directions”. Sobre esse ponto falo mais ao fim desse post!

Logo, quando falamos de CROSSFILTER podemos estar falando da função DAX CROSSFILTER ou do cross-filter do modelo de dados do Power BI. Um funciona independente do outro, mas a lógica por trás deles é a mesma.

CROSSFILTER

No post em que falei de Relationships eu fui breve nos comentários sobre relacionamentos many to many, mas lá eu disse que:

Fonte: https://www.sqlbi.com/tv/different-types-of-many-to-many-relationships-in-power-bi/ 49min de vídeo, mas assista!

Relacionamentos N:N entre dimensões, devem ser tratados usando CROSSFILTER BOTH.

Relacionamentos N:N entre dimensões e fato podem ser tratadas usando TREATAS ou com relacionamento N:N usando SINGLE direction.

Via de regra, evite utilizar cross-filter BOTH. Quando esta for sua única escolha prefira optar pela função DAX CROSSFILTER. Esse cenário geralmente ocorre quando você tem um relacionamento entre duas dimensões com uma tabela no meio (bridge-table ou factlessFact table) e quer filtrar uma tabela fato através de uma dimensão que não chega na fato, mas que se relaciona com outra dimensão que, essa sim, se relaciona com a fato.

Nesse caso um filtro BOTH entre as duas dimensões resolve o problem, pois permite que seu filtro navegue da dimensão que não se comunica com a fato até a próxima dimensão, que essa sim se comunica com a fato e então passa seu filtro até a fato.

Quando estiver em uma situação de N:N em que não exista uma terceira tabela ou bridge-table, vc pode criar um relacionamento N:N desde que o filtro seja SINGLE e na direção saindo da dimensão e apontando para fato. Uma outra opção nesse caso seria utilizar o DAX TREATAS, onde vc pode criar um relacionamento virtual, ou seja, suas tabelas não precisam se relacionar e vc ainda consegue criar um relacionamento entre elas.

Se você ler o quote acima com calma vai entender bem o cenário de uso do CROSSFILTER… Tem também falando sobre TREATAS, mas fica pra outro post.

Antes de mais nada, deixa eu reforçar… O uso de relacionamentos com cross-filter tipo BOTH pode, facilmente, gerar ambiguidade no seu modelo de dados. Um modelo é ambíguo quando há vários caminhos entre as tabelas. Em um modelo ambíguo, o mecanismo tem várias opções ao transferir um filtro de uma tabela para outra.

Na imagem acima temos um modelo ambíguo, porque a tabela DATE pode filtrar PURCHASES por dois caminhos diferentes:

• Diretamente pelo relacionamento dela com Purchases
• ou passando por SALES, depois por PRODUCT até filtrar PURCHASES novamente

Por isso que, em casos assim, é preferível criar uma measure utilizando a função DAX CROSSFILTER. Dessa forma apenas a medida em si será calculada utilizando a lógica do cross-filter both e o seu relacionamento, no seu modelo de dados, pode ser mantido como SINGLE, prevenindo qualquer tipo de ambiguidade.

DAX CROSSFILTER

Se verificarmos o que diz o manual sobre CROSSFILTER, vamos ver que essa função funciona assim:

CROSSFILTER(<columnName1>, <columnName2>, <direction>)

ColumnName1 é sua tabela N, ColumnName2 sua tabela 1, mas na real, ninguém realmente pensa nisso.

O importante é aqui: Direction, que pode ser Both, None ou OneWay.

• Both: vai se comportar como um relacionamento de cross-filter tipo BOTH.
• OneWay: vai se comportar como um relacionamento 1 pra N com o filtro saindo do 1 e indo pro N. (nunca vi ninguém usar esse argumento, mas blz…)
• None: não tem direção… (uso? se vc souber deixa nos comentários…)

• Importante você saber que CROSSFILTER só funciona se já exister uma relação no seu modelo de dados, ou seja, tem que ter aquela linhazinha entre uma tabela e a outra.
• CROSSFILTER ignora qualquer tipo de direção do relacionamento existente, ou seja, se suas duas tabelas já tem um relacionamento de direção SINGLE ou BOTH, isso não impacta em nada a função CROSSFILTER.
• Esta função não funciona com DirectQuery quando usada em colunas calculadas ou quando RLS estiver sendo aplicada naquele relacionamento.

Exemplo 1

Lembra lá do nosso modelinho de dados que usamos no post sobre many to many relationships? Então, vou usar ele novamente.

Imagina o cenário acima, 2 tabelas com cardinalidade N:N, mas com cross-filter direction tipo SINGLE de “GEO filters VENDAS”.

Como eu penso em aumentar esse modelo de dados e sei que vou adicionar novas tabelas e associar tanto com VENDAS quanto com GEO, não posso mudar minha direção pra BOTH no modelo de dados, porque isso vai com certeza gerar ambiguidade no meu modelo no futuro.

Só que, eu preciso calcular Vendas por Estado e População por Estado, e o resultado atual é conforme a imagem abaixo… POPULACAO faz um cartesiano e se repete pra todas linhas de VENDAS, porque o filtro é GEO -> VENDAS! Não existe VENDAS -> GEO!

Pra contornar esse cenário, podemos criar uma measure usando CROSSFILTER. Assim:

POPULACAO CROSSF = CALCULATE( SUM(GEO[POPULACAO]), CROSSFILTER(GEO[ESTADO (geo)], VENDAS[ESTADO (vendas)], Both))

Veja que a measure “POPULACAO CROSSF” mostra o valor correto, porque aplicar na execução da measure um cross-filter tipo BOTH, dessa forma habilitando que VENDA -> GEO!

Exemplo 2

Esse exemplo é muito bom, fonte: https://docs.microsoft.com/en-us/dax/crossfilter-function

O modelo de dados é o seguinte:

Perceba que é uma tabela fato e duas dimensões (DimDate e DimProduct). Então se quisermos filtrar qualquer coisa na tabela fato pelas dimensões vai dar certo. A dificuldade começa quando se quer mostrar, por exemplo, o COUNT de DimProduct por DATA, sendo que DATA (DimDate) não se relaciona com DimProduct.

Com o modelo do jeito que ele está, sem mudar nada, o resultado seria esse. Perceba que o Distinct Count de ProductKey faz um cartesiano!

Existem duas formas de resolver esse problema:

• Mudando o cross-filter da tabela DimProduct pra BOTH (que a gente não curte muito porque pode dar problema de ambiguidade se o modelo crescer)
• ou criando uma medida com CROSSFILTER (que é nosso objetivo nesse post!)

Vamos criar uma measure assim:

BiDi:=
CALCULATE(
[Distinct Count of ProductKey],
CROSSFILTER(FactInternetSales[ProductKey], DimProduct[ProductKey] , Both)
)

O resultado vai ser o da imagem abaixo. Perceba que agora a contagem distinta de Produtos ficou correta!

One to One Relationships (1:1)

Todos os relacionamentos um para um devem ser bidirecionais (usando cross-filter BOTH) – não é possível configurar de outra forma. Geralmente, não recomendamos a criação desses tipos de relacionamentos. Leia mais sobre 1:1 aqui.

Apply security filter in both directions

Sempre que um relacionamento com cross-filter é do tipo BOTH, abre uma opção de marcarmos “Apply security filters in both directions”. Essa opção garante que RLS quando aplicado nessa relação utilize um direcionamento SINGLE ao invés de BOTH.

RLS funciona com Import, também funciona pra DirectQuery e também pra Analysis Services, mas apenas on-premises. RLS não funciona pra Analysis Services com Live Connection, porque no Live Connection as regras já vem do Analysis Services, você não precisa configurar o RLS.

RLS e cross-filter BOTH funcionam filtrando dados. Os relacionamentos bidirecionais filtram explicitamente os dados quando as colunas são usadas em eixos, linhas, colunas ou filtros em um visual, por exemplo. Já o RLS filtra implicitamente os dados com base nas expressões definidas nas funções de RLS.

Então pensa assim… Se você tem duas tabelas se filtrando por cross-direction both e também tem RLS nelas, como é que você vai garantir que o filtro de segurança que você está aplicando (RLS) está efetivamente mostrando apenas o que a pessoa tem acesso?

Se você ler o whitepaper sobre Bidirectional cross-filtering in Analysis Services 2016 and Power BI, vai entender melhor. Mas vou tentar explicar aqui. Veja o seguinte modelo de dados:

Imagina que nesse modelo existe um RLS que diz o seguinte, RegionID = 1, ou seja, vamos filtrar a Region no RegionID = 1. Até aí tudo certo, porque REGION só filtra SALES, então fica fácil de garantir que só vou ver as SALES da RegionID = 1. Porém, se eu pegar CUSTOMER e tentar criar algum visual com essa tabela, eu vou tranquilamente conseguir ver tudo que está em CUSTOMER, no caso, a lista de CustomerIDs, assim:

Esse resultado da imagem acima era de se esperar, afinal não há filtros RLS definidos na tabela de CUSTOMER (apenas em REGION).

Porém, se eu arrastar pra esse visual SUM OF SALES vemos um resultado filtrado para apenas os CUSTOMERS que tem SALES na RegionID = 1.

Novamente, o resultado mostrado na imagem acima é esperado, pois definimos a RLS na tabela REGION. Isso filtrará automaticamente a tabela de SALES para apenas os valores da REGION que tenho acesso. Não se faz necessário ter um campo de Region no visual pra que o RLS funcione, porque SALES já é uma tabela filtrada por REGION, logo o RLS se perpetua de Region para Sales.

Agora imagine o seguinte… Imagine que existe um cross-filter direction tipo BOTH entre CUSTOMER e SALES… Imaginou? O que essa direção nessa relação entre CUSTOMER e SALES faz é permitir que que REGION filtre CUSTOMER.

Agora imagine se RLS funcionasse com BOTH, ia dar problema, porque aí você nunca iria conseguir ver os CUSTOMERS 2 e 3, de nenhum jeito, nem se você estivesse olhando a tabela de CUSTOMER sozinha, sem SALES nem REGION. É por isso que RLS só funciona apenas em direcionamento SINGLE.

Conclusão

Muito cuidado com uso de relacionamentos com cross-direction BOTH, pois podem criar ambiguidade no seu modelo! Procure sempre re-desenhar seu modelo para um star-schema com relações 1:N, mas se não for possível, busque sempre utilizar medidas com CROSSFILTER ao invés de aplicar cross-filter BOTH diretamente no seu modelo.

E além disso… Você pode ter um desempenho inferior ao usar a cross-filter BOTH com relacionamentos muitos para muitos.

Uma palavra de cautela em relação à filtragem cruzada bidirecional: você não deve habilitar relacionamentos de filtragem cruzada bidirecional, a menos que você entenda totalmente as ramificações de fazê-lo. Habilitá-lo pode levar a ambiguidade, sobreamostragem, resultados inesperados e potencial degradação do desempenho. (fonte: https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/6-relationships-cardinality)

Referências

https://powerbiexpert.wordpress.com/2020/07/28/power-bi-relationships/
https://docs.microsoft.com/en-us/dax/crossfilter-function
https://dax.guide/crossfilter/
https://www.sqlbi.com/articles/many-to-many-relationships-in-power-bi-and-excel-2016/
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
https://radacad.com/what-is-the-direction-of-relationship-in-power-bi
https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/6-relationships-cardinality
https://docs.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering