06-28-2017 02:35 AM - edited 06-28-2017 02:42 AM
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:
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 ?
Thanks in advance
06-29-2017 02:23 AM
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
07-03-2017 09:00 AM
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 ?
07-17-2017 02:23 PM - edited 07-17-2017 02:24 PM
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.
07-18-2017 12:06 AM
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.