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

BhaveshPatel's avatar
BhaveshPatel
Icon for Super User rankSuper User
9 years ago

You should look into the concept of disconnected tables to make it work the way you like.

 

More information can be found on this VIDEO.