Forum Discussion
Proper Calculation of Facts/Values from Dimensional Table
Source ( Download Here )
Userstory:
Imagine you have big star shema model and important values/facts in dimensional Tables.
And you don't want to mess up your fact table with multiple low cardinality columns.
Question: How to proper handle these dimTable facts/values?
Simplified Example:
Question: How to get DESIRED YearVolume (because by default it gives 2200 (Totals) in each cell)?
Solution ( Download Here )
DESIRED YearVolume = CALCULATE( SUM(dimSalesYearly[YearVolume]), KEEPFILTERS(factSales) )It could be used as simple pattern for similar cases
Advanced Example Solution:
If you have a bit more complicated model:Sometimes you need fixed Totals as well:
DESIRED YearVolume (Fixed Totals) = VAR isInSc_Prod = ISINSCOPE(dimProduct[idProduct]) VAR isInSc_Year = ISINSCOPE('Calendar'[Year]) VAR Result = SWITCH( TRUE(), NOT isInSc_Prod && NOT isInSc_Year, CALCULATE([DESIRED YearVolume],REMOVEFILTERS(dimProduct[idProduct]),REMOVEFILTERS('Calendar'[Year])), NOT isInSc_Prod, CALCULATE([DESIRED YearVolume], REMOVEFILTERS(dimProduct[idProduct])), NOT isInSc_Year, CALCULATE([DESIRED YearVolume], REMOVEFILTERS('Calendar'[Year])), [DESIRED YearVolume] ) RETURN ResultP.S. I'd appreciate if you support "PowerQuery: Special symbol for previous step name" request feature by your vote here or/and LinkedIn like/repost here.
5 Replies
- tamerj1
Community Champion
Hi Anonymous
how about
DESIRED YearVolume 2 = SUMX ( SUMMARIZE ( factSales, dimSalesYearly[idYearProduct], "@Volume", SUM ( dimSalesYearly[YearVolume] ) ), [@Volume] )?
tamerj1 Anonymous I came up with:
Measure = VAR __idYearProduct = DISTINCT('factSales'[*idYearProduct]) VAR __Result = SUMX(FILTER(ALL('dimSalesYearly'), [idYearProduct] IN __idYearProduct), [YearVolume]) RETURN __ResultOr, you know, make the relationship bi-directional would work as well.
NotGregDeckler , tamerj1
Unbelievable!
Absoulutely different thinking style 🙂👍
I updated Source file with your solutions.
Thank you very much!
P.S. Not clear from this discussion branch, but I asked tamerj1 and NotGregDeckler in this post, if it's possible to create this measure without explicite use of CALCULATE function according to DAX is easy, CALCULATE makes DAX hard phylosophy.
Solution ( Download Here )
DESIRED YearVolume = CALCULATE( SUM(dimSalesYearly[YearVolume]), KEEPFILTERS(factSales) )It could be used as simple pattern for similar cases
Advanced Example Solution:
If you have a bit more complicated model:Sometimes you need fixed Totals as well:
DESIRED YearVolume (Fixed Totals) = VAR isInSc_Prod = ISINSCOPE(dimProduct[idProduct]) VAR isInSc_Year = ISINSCOPE('Calendar'[Year]) VAR Result = SWITCH( TRUE(), NOT isInSc_Prod && NOT isInSc_Year, CALCULATE([DESIRED YearVolume],REMOVEFILTERS(dimProduct[idProduct]),REMOVEFILTERS('Calendar'[Year])), NOT isInSc_Prod, CALCULATE([DESIRED YearVolume], REMOVEFILTERS(dimProduct[idProduct])), NOT isInSc_Year, CALCULATE([DESIRED YearVolume], REMOVEFILTERS('Calendar'[Year])), [DESIRED YearVolume] ) RETURN ResultP.S. I'd appreciate if you support "PowerQuery: Special symbol for previous step name" request feature by your vote here or/and LinkedIn like/repost here.
