cumulative sum using related lookup table

[palvarez83]

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?

[v-chuncz-msft · Accepted solution]

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

[palvarez83]

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.