cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
palvarez83 Regular Visitor
Regular Visitor

cumulative sum using related lookup table

Hello,

I would like help using a related table to help me find a cumulative sum.

 

I have a fact table fGALT which has various columns including  fGALT[Flange Qty]  which is a whole number and fGALT[Size] which is a pipe size in text format such as 1", 1-1/2", 2" ...

 

I have a related table dPipeSize which is primarily used as slicer.  dPipeSize has a unique list of pipe sizes (e.g. 1", 1-1/2", 2" ...) and a corresponding size code column as an integer 1, 2, 3, 4, ect.....   dPipeSize table is related to the fact table using a 1 to many relationship size (e.g. 1", 1-1/2", 2" ...).  The size code allows me to sort the inch based sizes nicely.

 

I would like to write a DAX measure that allows me to sum up all the cumulative number of flanges <= a given size on a table.  If I were to flatten the fact table it is easy, but I can't seem to do it with the related table.  If I flatten the fact table ( look up the size code for each pipe size I can write a measures as follows.

 

Flanges Tested :=
SUM ( fGALT[Flange Quantity] )

Then for the cumulative quantity on the flatted table I would write:

cumulative Flange Tested :=
CALCULATE (
    [Flanges Tested],
    FILTER ( ALLSELECTED ( fGALT ), fGALT[Size Code] <= MAX ( fGALT[Size Code] ) )
)

If I do not flatten the table, and instead wanted to look up the size by size code on the related table, what is the proper syntax?  Here is what I tried and failed with.

cumulative Flange Tested :=
CALCULATE (
    [Flanges Tested],
    FILTER (
        ALLSELECTED ( fGALT ),
        RELATED ( fGALT[Size Code] ) <= MAX ( RELATED ( fGALT[Size Code] ) )
    )
)

I got an error message on that measure.  Any ideas on how I can correct that?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: cumulative sum using related lookup table

@palvarez83 ,

 

You may refer to the DAX below.

cumulative Flange Tested =
VAR s =
    MAX ( dPipeSize[Size Code] )
RETURN
    CALCULATE (
        [Flanges Tested],
        FILTER ( ALLSELECTED ( fGALT ), RELATED ( dPipeSize[Size Code] ) <= s )
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: cumulative sum using related lookup table

@palvarez83 ,

 

You may refer to the DAX below.

cumulative Flange Tested =
VAR s =
    MAX ( dPipeSize[Size Code] )
RETURN
    CALCULATE (
        [Flanges Tested],
        FILTER ( ALLSELECTED ( fGALT ), RELATED ( dPipeSize[Size Code] ) <= s )
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
palvarez83 Regular Visitor
Regular Visitor

Re: cumulative sum using related lookup table

Looks like I didn't need the related function at all.  My solution was filtering on the related dimension table instead.

 

cumulative Flange Tested :=
CALCULATE (
    [Flanges Tested],
    FILTER (
        ALL ( PipeSize ),
        RELATED ( PipeSize[Size Code] ) <= MAX ( RELATED ( PipeSize[Size Code] ) )
    )
)

Thank you for the help.