Arrange Data

[jasgun]

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 GBP

So for example if "QTREND" is selected the AMT-GBP for the 1st row would be 300/1.71 = 175.44

                         if "SBF"         is selected the AMT-GBP for the 1st row would be 300/1.96 = 153.06

The problem I have is I cannot create a relationship as the CCY is not unique.

Do I need to rearrange the way the data is layed out? However there is potential for more "RateType" to be added.

Thanks

Jason

[v-haibl-msft · Accepted solution]

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

Perfect, thanks again Herbert!

[BhaveshPatel]

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.