Forum Discussion
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 below you can see what my question is exactly:
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!!
2 Replies
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!!
Thank you VahidDM, this formula works exactly as hoped. 😀
