DAX Measure Performance and Many-To-Many

[Datatouille]

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 🙂 ) 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 🙂

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

Thanks in advance 🙂

Tristan

[MattAllington]

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. 

[Datatouille]

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.

[v-chuncz-msft]

@Datatouille,

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.

[Datatouille]

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 ?