Forum Discussion

brief001's avatar
brief001
Helper II
4 years ago
Solved

Sum using two relation tables

I'm looking for a DAX formula that calculates the sum using two other tables. One table shows how often a certain code occurs. And in a dimension table is the duration of this code. In the example be...
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi brief001 

     

    Try this:

    Measure =
VAR _Dur =
    ADDCOLUMNS(
        'Table 2',
        "Dur1",
            [Amount]
                * ( LOOKUPVALUE( 'Table 3'[Duration], 'Table 3'[Code], 'Table 2'[Code] ) )
    )
RETURN
    SUMX( _Dur, [Dur1] )

     

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

    Appreciate your Kudos!!

     

VahidDM's avatar
VahidDM
Super User
4 years ago

Hi brief001 

 

Try this:

Measure =
VAR _Dur =
    ADDCOLUMNS(
        'Table 2',
        "Dur1",
            [Amount]
                * ( LOOKUPVALUE( 'Table 3'[Duration], 'Table 3'[Code], 'Table 2'[Code] ) )
    )
RETURN
    SUMX( _Dur, [Dur1] )

 

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

Appreciate your Kudos!!

 

  • brief001's avatar
    brief001
    Helper II
    4 years ago

    Thank you VahidDM, this formula works exactly as hoped. ğŸ˜€