How can I ignore the table context?

DAX DAX Calculations
1

Hi all,

I have below Power BI matrix which showing the sales performance by Customer (like Excel Pivot).

But I would like to show the sales Amt in HKD or in Local Currency depends on RSO selected in filter panel.

ie) if only DACH is selected, it should show in Local currency
if more than 1 RSO is selected, it can show in HKD

No need to apply any exchange rate as the raw data already had sales amt in local currency and sales amt in HKD

But i try to use if function to control it

Selected RSO Numbers= CALCULATE(DISTINCTCOUNT(‘DIM-Customers’[BU]),ALL(‘DIM-Customers’)
Sales Amt =if([Selected RSO Numbers]>1,[Sales (HKD)],[Sales (Local Currency)])

it always showing result in Local currency as I believe it due to table context. How can I ignore the table context and only use the selected RSO numbers for my DAX formula?

Layout
Layout1507×784 74.2 KB

2

Hi @chiu2003,

It’s very difficult to understand what’s happening under the hood without looking at data model. Would really appreciate if you can provide some sample data/pbix file to work on solving this problem. Thanks!

3

Hi hafizsultan,

Thanks. I can upload it.

I try to use hasonefilter, it can fix 90% of problem.

However, when the user choose 2 RSO with single currency, the report will show HKD…
ie: Choose DACH & FRANCE, report will show sales AMT in HKD, but I want them show in EUR

Below is the RSO and currency mapping

DACH=EUR
EU DIST=USD
EU ECOM=GBP
FRANCE=EUR
UK=GBP

4

Hi,

I suggest to watch the below video, Sam has published a few times about applying currency conversion.

Paul

1 Like
5

Hi again,

You may also want to consider the TREATAS function, that allows to get values from an unrelated table.

Paul

1 Like
7

Hi @chiu2003,

Thanks for posting your file. You just need to change below measure. Due to filter from table’s row on ‘DIM-Customers’[Customer Description], it was always giving 1. So, after changing this base measure, your other measure is working perfectly fine.

Num of Currency = 

CALCULATE(DISTINCTCOUNT('DIM-Customers'[BU Local Currency]),ALL('DIM-Customers'[Customer Description]))
8

Hi @chiu2003, we’ve noticed that no response has been received from you since 2nd of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arises.