Forum Discussion
Helper IIMeasure Optimization Help
jscottNRG, this looks pretty complex. A couple of things come to mind right away:
You have a LOT of bi-directional relationships. These can sometimes produce results that are unexpected, wrong, or lead to slow measures. I typically avoid bi-directional filters unless they're truly necessary.
You mention that Jobs is a fact table, and you're relating it to other fact tables (which is likely why you need the bi-directional filters). This is NOT a best practice when designing your data model. Fact tables should relate to dimension tables. There are other ways to use DAX to "harvest" a value from another fact table (typically using FILTER() and SELECTEDVALUE() or variables).
There are a lot of relationships, period. Are ALL of these tables necessary? Can you group/combine/merge some of these tables together in Query Editor?
You may want to use some SQL to join some of these tables into a single fact table. Aim for a star schema.
The community (myself included) is willing to help you, but you'll need to provide us some more information first. Can you post the file on dropbox? If not, can you make the data anonymous first? Can you send a screenshot of each of the tables that are involved in the measures / calculated tables you're looking for?
It's extremely hard to troubleshoot DAX when we don't know what the data looks like, or what the result should be.
Cheers,
~ Chris
Helper IIWow Anonymous thank you again for your thorough guidance ond offer to help on this!
I've tried to make the changes you suggested:
- join fact tables in the Query Editor to reduce the number of relationships (i.e. instead of using relationships between RoughInspections and RoughAirBarrier, RoughAirSealing, RoughCustom, etc. they are joined at the query stage into a fact table, "RoughInspections_merged")
- switch bi-directional relationships cross filter direction from "Both" to "Single" where possible (there are some where PBI says "the cardinality you selected isn't valid for this relationship" when I try to do this and I don't understand why)
- use dimension tables to relate to fact tables for slicer use -- there are still a few fact-to-fact table relationships that I'm not sure how to properly fix
Still, after these changes, my "Rough Inspection Results" page takes between 50-60 seconds to update visuals when a slicer selection is made. I've made an anonymous dataset and uploaded my Power BI file to Google Drive so others can access to review. Note that I first made the changes you recommended to the "Rough" part of the dataset; there are a lot of measures to update so I wanted to see if I could improve the performance before editing the rest. I deleted the relationships on the "Final" side so I could be sure that it wasn't those relationships slowing things down.
Please let me know if I can explain anything or if any part of my problem is unclear. I'm hopeful that the performance of my report can be improved, but maybe I'm just asking too much of Power BI? Thanks again to the Power BI community for helping a rookie like myself out!
Jonathan
- jscottNRG
Adding a comment to bump this to the top of the forum homepage, I'm still looking for help determining what issues remain in my report.