Forum Discussion
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 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
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
3 Replies
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.
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
Perfect, thanks again Herbert!
