Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jasgun
Frequent Visitor

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.

 

Capture.PNG

 

Thanks

 

Jason

1 ACCEPTED SOLUTION
v-haibl-msft
Microsoft Employee
Microsoft Employee

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

Arrange Data_1.jpg

 

Best Regards,

Herbert

View solution in original post

3 REPLIES 3
v-haibl-msft
Microsoft Employee
Microsoft Employee

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

Arrange Data_1.jpg

 

Best Regards,

Herbert

Perfect, thanks again Herbert!

BhaveshPatel
Community Champion
Community Champion

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.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.