Forum Discussion

jasgun's avatar
jasgun
Frequent Visitor
9 years ago
Solved

Arrange Data

Hi all,     I have the below setup. What I'm trying to get to is if the user selects "QTREND" from the rate type slicer, I would like a new measure "AMT-GBP" to calculate the "AMT" converted to ...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    jasgun

     

    We can use LOOKUPVALUE Function (DAX) to lookup the corresponding RoE in another table with no relationship.

     

    AMT-GBP = 
VAR SelectedRate =
    IF ( HASONEVALUE ( RoE[Rate Type] ), VALUES ( RoE[Rate Type] ) )
VAR CurrentCCY =
    CALCULATE ( LASTNONBLANK ( Policy[CCY], Policy[CCY] ) )
RETURN
    CALCULATE (
        SUM ( Policy[AMT] )
            / LOOKUPVALUE ( RoE[RoE], RoE[Rate Type], SelectedRate, RoE[CCY], CurrentCCY )
    )

     

    Best Regards,

    Herbert

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

jasgun

 

We can use LOOKUPVALUE Function (DAX) to lookup the corresponding RoE in another table with no relationship.

 

AMT-GBP = 
VAR SelectedRate =
    IF ( HASONEVALUE ( RoE[Rate Type] ), VALUES ( RoE[Rate Type] ) )
VAR CurrentCCY =
    CALCULATE ( LASTNONBLANK ( Policy[CCY], Policy[CCY] ) )
RETURN
    CALCULATE (
        SUM ( Policy[AMT] )
            / LOOKUPVALUE ( RoE[RoE], RoE[Rate Type], SelectedRate, RoE[CCY], CurrentCCY )
    )

 

Best Regards,

Herbert

jasgun's avatar
jasgun
Frequent Visitor
9 years ago

Perfect, thanks again Herbert!