Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Experts,
Previously we had a PowerBi dashboard based on a large fact table. However, recently we have revamped the backend data warehouse with Star Schema. My previous DAX measure had few filters to several rows in a large fact table (VW_Water) like below,
MeasureDryOverflow = CALCULATE(DISTINCTCOUNT('VW_Water'[CC_MASTER_ID]),
FILTER('VW_Water','VW_Water'[CC_MAST_TYPE]= "WASTE WTR"),
FILTER('VW_Water','VW_Water'[CC_MAST_SUBTYPE]= "WASTE WTR*OVERFLOW"),
FILTER('VW_Water','VW_Water'[QUESTIONS]= "DRY"))
Now, I have connected to new Star Schema DW with "import" mode and created a measure like below (with filters to relational data tables),
MeasureDryOverflow = CALCULATE(DISTINCTCOUNT('DW VW_NFPM_FactRFS'[CC_MASTER_ID]),
FILTER('STAGING CC_TYPES','STAGING CC_TYPES'[CC_T_ID]= "WASTE WTR"),
FILTER('STAGING CC_SUBTYPES','STAGING CC_SUBTYPES'[CC_SUBTYPES__ID]= "WASTE WTR*OVERFLOW"),
FILTER('STAGING CC_MASTER__CC_MAST_QUEST_TEXT','STAGING CC_MASTER__CC_MAST_QUEST_TEXT'[CC_MAST_QUEST_REPLY]= "DRY"))
This new measure is yielding the same results as the previous measure, but my new PBI dashboard is really slow and very large compared to the previous dashboard. What is the best and correct way of creating measures when it comes to Star Schema-based data source? Any suggestions to improve my dashboard?
Thank you
Challenge yourself to justify the decision for the bidirectional search filter. That is not good practice for a hierarchical data model. You need to have good plausible reasons to need that.
Thanks @lbendlin for your reply. In my model I've used "Import" method and Power BI automatically identified my table/view relationships (see below relationship map). Most of the relationships are "Many:1" (Bothway)
Your original measure can be rewritten since all the filters run against the same table
MeasureDryOverflow = CALCULATE(DISTINCTCOUNT('VW_Water'[CC_MASTER_ID]),
'VW_Water'[CC_MAST_TYPE]= "WASTE WTR",
'VW_Water'[CC_MAST_SUBTYPE]= "WASTE WTR*OVERFLOW",
'VW_Water'[QUESTIONS]= "DRY")
your new situation is interesting. Can you debug it by adding the dimension filters one by one? (You have a 1:M relationship between your new dimensions and the facts table, right?)
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |