Reply
Established Member
Posts: 194
Registered: ‎04-13-2016

DAX Measure Performance and Many-To-Many

[ Edited ]

Hello everyone,

 

I am currently preparing a webinar on 'Many to Many in DAX' for the 2nd French edition of the 24 Hours Of PASS: 24 webinars of one hour each dedicated to Microsoft technologies around Data & BI.

I am using 4 different DAX approaches (Bi directional [global and local] filter, Context Transition, Marco Russo's Black Magic with Bridge expanded table) to tackle the 'Many to Many issue'. I am trying to compare their performance with DAX Studio.

Several questions came to me while preparing this session. I will list them below:

 

DAX Studio

1. Why is my first test in DAX Studio always much longer than the subsequent ones ? For example, I am running the following statement:

Evaluate Row("Test" , [BridgeMethod]). The first time I run this script, I always get a response time which is approximately 10x bigger than the others. Is it because DAX Studio needs to connect to the underlying Power BI data model just for the first attempt ?

 

2. I notice that I get quite big response time variances for the same measure even when I clear the cache before each attempt (ok it is in milliseconds, but it can double or triple). How is that possible ? 

 

3. I also notice that the ventilation between Storage Engine (SE) and Formula Engine (FE) changes a lot for the same measure.

For example, I execute Evaluate Row("Test" , Calculate( [Total Sales] , BridgeTable) ) and I get 57% FE  - 43% SE. I clear the cache, execute the same query and I get 100% FE - 0% SE. It may explain my question 2. but I cannot figure out how is that possible ? I would expect this FE/SE allocation to be somewhat identical as the DAX query plan is the same (isn't?).

 

4. Is it possible to launch 1 000 (or more Smiley Happy ) identical queries at once in DAX studio and get the distribution of response time ? Or is it in the roadmap ? The idea is to get a quick overview of the variance of a measure.

 

Many-To-Many and Row-Level Security

I read a lot about this topic and I will obviously cover it during my session. But, I still have a question:

Why do I have to tick the preview feature 'Enable cross filtering in both directions for DirectQuery' to make it work in Power BI Desktop even when my use case has NOTHING to do with DirectQuery ?

I created some roles with filter conditions based on my RLS table in Power BI Desktop - All my sources are local Excel files. How come DirectQuery enters into the equation here ?

 

I am sure you guys @marcorusso & @AlbertoFerrari or @cwebb, @kdejonge have the answers Smiley Happy

And @LaurentCouartou@MattAllington you might be interested as well (I guess).

 

Thanks in advance Smiley Happy

Tristan

 

 

 

 

Community Support Team
Posts: 4,198
Registered: ‎07-09-2016

Re: DAX Measure Performance and Many-To-Many

@Excelside,

 

Visit http://daxstudio.codeplex.com/discussions for support on DAX Studio issue. While for the preview feature, you may give feedback via https://ideas.powerbi.com/forums/265200-power-bi.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Established Member
Posts: 194
Registered: ‎04-13-2016

Re: DAX Measure Performance and Many-To-Many

Hi,

 

Thanks for the links.
I'll post my DAX performance questions on DAX studio's forum.

 

I had a look at my DirectQuery issue and haven't found any answer yet.

Any other suggestions ? Solutions ?

Super User
Posts: 1,631
Registered: ‎07-03-2015

Re: DAX Measure Performance and Many-To-Many

[ Edited ]

1.  Are you clearing the cache for the second run?  I would expect this to be a cache benefit on 2+.  If you are clearing the cache, then I have no idea 

2. You cannot rely on time accuracy below a few hundred milliseconds.  This is taught by Marco in Optimising DAX

3.  I wonder if the cache clearing is working - I dont know. 

4. Dont know

5. I ma not aware of the problem. 

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Established Member
Posts: 194
Registered: ‎04-13-2016

Re: DAX Measure Performance and Many-To-Many

Hi @MattAllington

 

Thanks for your reply.

Yes I am clearing the cache !

 

Enable CrossFilter in DirectQuery is no longer a preview feature but I still do not understand why I need to tick it to make Row-Level Security and Many-To-Many work even when I am not in DirectQuery mode.