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

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
chinthada
Helper I
Helper I

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

Anonymous
Not applicable

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."

chinthada
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.

 

 

kumar27
Advocate V
Advocate V

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