Forum Discussion
Poor Measure Performance and Confusion
vgeldbr,
First of all, I got so annoyed with all the errors in my DAX that I produced my own small sample dataset so I could get my measure correct at least. I was able to but in looking at your question again, I noticed something I glossed over earlier that may be a much more productive path forward on your issue.
Why does your dimension table have 24x more records than your fact table? The inverse seems much more standard. I'm not sure what the data source on your engagement codes is but can you filter out all the codes that aren't in your fact table upstream (either in the data source itself or in Power Query in your Power BI semantic model)?
Wilson_ , the dimension table is a list of financial codes. Each code has many attributes that are required in the report (e.g. status, description, owner). I will need these. I'm focused though just on the immediate problem is just using the engagement code (which is the key for the relationship) causes the performance to grind to a crawl.
vgeldbr,
In my humble opinion, that is part of the immediate problem. When you're switching from using the engagement code from the fact table to using it from the dimension table instead, Power BI is not just using a field with 24x more rows, it is likely using a field with many times more than that if you only consider unique values (I'm guessing it's unique in your dimension table and not in your fact table).
Wilson_ you are correct. Engagement Code is unique in the Engagement Code Master table. It is not unique in the fact table.
So this leaves me with how to address this. Ultimately in my visual I need to show data from the Engagement Codes Master like the "type", "name", "owner". As soon as I use any value from that table then the First Month measure stops performing reasonably.
I'm stuck...
vgeldbr,
Without knowing the contents of the lookup table or where the data is sourced or what you have permissions to do, the only thing I can suggest is you should find a way to filter down the number of records in your model. All I can tell with the information I have so far is over 95% of your dimension table doesn't actually have any rows in the fact table. Is it possible to go to the data source or to Power Query to filter out all the records in the dimension table that have no rows in the fact table?
I responded a few days ago to my original post not the response from Wilson_ .
I will have to look at the option but I still don't understand why adding the measure causes the issue in the first place since all of the relevant columns relate to the fact table. I don't understand what the DAX is doing and why it is simpacted by the large dimension table in the first place.
It's not feasible to do this but I'm hoping somebody can explain what I'm clearly missing in terms of data modelling and why using any column other than the key column from the Master Engagement Codes column would present this performance problem only when I use a measure. Simply adding additional columns to to my table visual from Engagement Master works just fine. And of course I'm still keen for any suggested ideas for how to address.
Hi vgeldbr ,
Wilson_ 's answer is very good , and I want to share other solutions:
Firstly:
Measures in DAX are dynamically calculated in response to user interactions and filters. When you add a metric value, it interacts with the entire data model.If your metric value references columns in both the fact data table and the dimension table, the impact on performance can be significant.DAX evaluates these relationships during query execution.The larger the dimension table, the more complex the calculations become, which affects query response time.
Please consider using DAX Profiler or other performance tools to identify bottlenecks and optimize the model.
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.