Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chinthada
Helper I
Helper I

Best approach to build Dax measures for Star Schema Data Model

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

3 REPLIES 3
lbendlin
Super User
Super User

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.

chinthada
Helper I
Helper I

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)

chinthada_0-1605058678540.png

 

chinthada_1-1605058980077.png

 

lbendlin
Super User
Super User

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?)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors