Forum Discussion
Poor Measure Performance and Confusion
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.