topic Re: What is the best approach to build Dax measures for Star Schema Data Model in DAX Commands and Tips

What is the best approach to build Dax measures for Star Schema Data Model

chinthada — Tue, 10 Nov 2020 03:51:45 GMT

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

Re: What is the best approach to build Dax measures for Star Schema Data Model

kumar27 — Tue, 10 Nov 2020 05:19:35 GMT

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.

Re: What is the best approach to build Dax measures for Star Schema Data Model

chinthada — Tue, 10 Nov 2020 20:37:16 GMT

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.

Re: What is the best approach to build Dax measures for Star Schema Data Model

kumar27 — Wed, 11 Nov 2020 06:21:04 GMT

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.

Re: What is the best approach to build Dax measures for Star Schema Data Model

Anonymous — Wed, 11 Nov 2020 13:00:18 GMT

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

Re: What is the best approach to build Dax measures for Star Schema Data Model

chinthada — Tue, 17 Nov 2020 20:53:15 GMT

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