cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Proper Calculation of Facts/Values from Dimensional Table

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

``````DESIRED YearVolume =
CALCULATE(
SUM(dimSalesYearly[YearVolume]),
KEEPFILTERS(factSales)
)``````

It could be used as simple pattern for similar cases

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

P.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 5
Super User

Hi @Anonymous

``````DESIRED YearVolume 2 =
SUMX (
SUMMARIZE (
factSales, dimSalesYearly[idYearProduct],
"@Volume", SUM ( dimSalesYearly[YearVolume] )
),
[@Volume]
)``````

?

Regular Visitor

@tamerj1 @Anonymous I came up with:

``````Measure =
VAR __idYearProduct = DISTINCT('factSales'[*idYearProduct])
VAR __Result = SUMX(FILTER(ALL('dimSalesYearly'), [idYearProduct] IN __idYearProduct), [YearVolume])
RETURN
__Result``````

Or, you know, make the relationship bi-directional would work as well.

Anonymous
Not applicable

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

Anonymous
Not applicable

``````DESIRED YearVolume =
CALCULATE(
SUM(dimSalesYearly[YearVolume]),
KEEPFILTERS(factSales)
)``````

It could be used as simple pattern for similar cases

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

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

Community Support

Hi @Anonymous ,

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors