Handling Bi-directional Relationship

Power BI Data Model
1

Hello guys,

Let’s say I have a simple model and I fully understand how bi-directional filtering works and I really need to optimize my model performance.

So, what is the best way, and why so?

The options are:

Opt-1: Enabling Bi-directional cross-filtering ?
Opt-2: Use CROSSFILTER() function instead ?

Notes: I do understand that bi-di are troublesome and we must avoid using it but, I just need to understand what is the best approach when we have a simple model. So, this means my model is simple not the dataset, so it could have millions of rows and 20-30 columns.

2

@quantumudit,

Good question! I was hoping to find a conclusive SQLBi article on this issue, but all I could locate was the following:

https://community.powerbi.com/t5/Desktop/Bidirectional-filtering-or-crossfilter/td-p/761852

Have you tried running it both ways through Performance Analyzer, and seeing which one produced the faster results?

@Nick_M and @AntrikshSharma are the resident DAX optimization gurus, so I would be curious as to what they would have to say about this question.

  • Brian
1 Like
3

@BrianJ

I also asked the same in the Power BI Community and got some amazing responses.
Have a look : Resolving Bi-Dis

Would love to see what you guys think.

4

Crossfilter would be the best in terms of performance. This has to do how the two engines (Formula and Storage) handle the bi-directional filters. Basically, the bi-directional filters require more work by those two engines which can reduce the performance. So i would go with crossfilter when possible. Now the catch here is that every measure you write needs to have the crossfilter predicate in it since that filter wouldnt be automatically propagated like other relationships. Which that in itself can be hard to maintain and generally a pain. Just need to way the pros and cons. I would run performance metrics using both the crossfilter and bidirectional.

Id be more concerned with the 20-30 columns in your tables though, but thats another discussion.

2 Likes
5

Ha Ha, @Nick_M … It’s just a hypothetical scenario so please don’t concern about 20-30 columns, I would never like to have so many in my table :grinning:

But, really like your detailed explanation.

Now, what about RLS?

Never faced such a situation fortunately but, will it work without BI-DI?

and how to resolve it? TREATAS() ?

6

I don’t think there will be any performance difference in CROSSFILTER vs Bi-Directional filtering, however TableExpansion proves to be much efficient in contoso dataset. I the below example I am trying to get the list of Categories from which each customer bought.

2
21018×668 43.2 KB

image
image873×691 18 KB

Here are the queries generated by

Bi-Directional filtering:

Which Category they bought from = 
CONCATENATEX ( 
    VALUES ( ProductCategory[Category] ),
    ProductCategory[Category],
    ", "
)

1
11257×290 31.2 KB

CROSSFILTER ( no difference in both )

Which Category they bought from CrossFilter = 
CALCULATE (
    CONCATENATEX ( 
        VALUES ( ProductCategory[Category] ),
        ProductCategory[Category],
        ", "
    ),
    CROSSFILTER ( Sales[ProductKey], Products[ProductKey], Both ),
    CROSSFILTER ( Products[ProductSubcategoryKey], ProductSubcategory[ProductSubcategoryKey], Both ),
    CROSSFILTER ( ProductSubcategory[ProductCategoryKey], ProductCategory[ProductCategoryKey], Both )
)

Crossfilter
Crossfilter1287×268 31.2 KB

Table Expansion: Wins the match

Which Category they bought from ExpandedTable = 
CALCULATE (
    CONCATENATEX ( 
        VALUES ( ProductCategory[Category] ),
        ProductCategory[Category],
        ", "
    ),
    Sales
)

TableExpansion
TableExpansion1237×258 22.7 KB

image
image1591×266 47.3 KB

So the answer is “it depends”, one code will be not be the best one for every model you have to play with it and find what works best for you.

3 Likes
7

Whooo !!! @AntrikshSharma
Well explained… Thanks for this… :grinning:

8

Marco and Alberto talk about this quite a bit on SQLBI. The issue is primarily is ambiguity in the model. The only place I have bidirectional turned on is between fact tables like in challenge 7.

1 Like