cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

What is the 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

5 REPLIES 5
Helper I
Helper I

@kumar27 @daxer As I mentioned above my dashboard design won't allow to use visual slicers or filterpane filters. 

Solution Sage
Solution Sage

One of the worst things you can do in Power BI (and you're doing it) is to put a full table in a filter under CALCULATE. Never do this unless you want to experience extreme slowness of measures. The golden rule of DAX says: You should never filter a table when you can filter a column. Please stick to this rule religiously. If you want to know why this is so, you must grab the book by The Italians "The Definitive Guide to DAX."

Helper I
Helper I

@kumar27 Thanks for your reply. Can you explain more about your first point.

 

Also, I cant use filter conditions from slicers in my dashboard. This is because I am using slicer based measure selection and my number of measures are high. 

chinthada_0-1605040544817.png

 

If would have a created a Metric  as 

 

MeasureDryOverflow = DISTINCTCOUNT('DW VW_NFPM_FactRFS'[CC_MASTER_ID])

And then I would have used the filters as onto the dashboard itself :

CC_T_ID= "WASTE WTR"
CC_SUBTYPES__ID]= "WASTE WTR*OVERFLOW"
CC_MAST_QUEST_REPLY= "DRY"

 

from the filter pane to the right not in the slicers ., that would speed up the performance a bit , and certainly the best approach for star schema if you have multiple metrcis with different filters on these columns itself.

 

 

Advocate II
Advocate II

Ideally, you should not write explicit filter conditions on DAX , if you have star schema then your metrics created in the fact tabe should be able to sliced and diced by dimensions.

 

Ans then you can handel this selected items that is inside your filter condition from Slicers.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.