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
DenisSipchenko
Advocate III
Advocate III

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:

DenisSipchenko_0-1679666252886.png

PBIDesktop_xAMVu5f1dc.png

Question: How to get DESIRED YearVolume (because by default it gives 2200 (Totals) in each cell)?

 

1 ACCEPTED SOLUTION
DenisSipchenko
Advocate III
Advocate III

Solution Download Here )

PBIDesktop_z1nOxjeTbn.png

 

 

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:

DenisSipchenko_0-1679669068192.png

Sometimes you need fixed Totals as well:DenisSipchenko_1-1679669133103.png

 

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.

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

Hi @DenisSipchenko 

how about 

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

?

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

 

@NotGregDeckler , @tamerj1 
Unbelievable!
Absoulutely different thinking style 🙂👍
I updated Source file with your solutions.

DenisSipchenko_0-1680080978459.png

 


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. 

DenisSipchenko
Advocate III
Advocate III

Solution Download Here )

PBIDesktop_z1nOxjeTbn.png

 

 

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:

DenisSipchenko_0-1679669068192.png

Sometimes you need fixed Totals as well:DenisSipchenko_1-1679669133103.png

 

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.

Hi @DenisSipchenko ,

 

Thank you for your sharing. Please kindly accept your workaround as the solution. Then more people will see your post.

 

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.

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