Stuck with DAX-ing slicer selection

[EugeneNovikov]

Hi all, sitting a few days and no light in that tunnel... 

I have a simple ledger-like report. My entities are:

1. Accounts, can be in one of the 3 currencies: UAH, USD, EUR, where UAH is 'native' for the report and USD and EUR are 'foreign'
2. Currencies.
3. Ledger, which contains dated transactions in two fields:

a) native: always in UAH

b) foreign: in any of the 3 above currencies, if the account is in UAH, then the amounts in both fields coincide

4. Calendar

All 4 entities are connected by 1:n relationships as in the attached file. Note that the relationship between Accounts and Currencies is bi-directional. 

What I want to see in the report is described in the image but generally it's this rule to select Amount column for summing up [.Amount total] measure: 

1. Slicer clear or 

    One on more accounts in UAH selected or
    One or more accounts selected in two or more different currencies: use only data on Ledger[Amount UAH]

2. One or more accounts all in same single foreign (USD or EUR) currency selected: use only data on Ledger[Amount !UAH]

I also need a way to show the currency in which calculation was performed and have non-working measure [.Currency] for that. 

Files: 

Report image
PBIX

Any suggestions warmly welcome... thank you.

[Rupak_bi · Accepted solution]

Hi @EugeneNovikov ,

Hope this solves your issue.

Modify two measure as below

.Currency =

VAR max_Currency = max(  'Currency'[Currency])

VAR min_Currency = min(  'Currency'[Currency])        

Var check = if(max_Currency=min_Currency,max_Currency,"UAH")

RETURN

check

.Amount total=

VAR max_Currency = max(  'Currency'[Currency])

VAR min_Currency = min(  'Currency'[Currency])        

Var check = if(max_Currency=min_Currency,max_Currency,"UAH")

var check1 = switch(check,"USD","!UAH","EUR","!UAH","UAH","UAH","UAH")

RETURN

SWITCH(check1,"UAH",CALCULATE(SUM(Ledger[Amount UAH])),"!UAH",CALCULATE(SUM(Ledger[Amount !UAH])),"NA")

If this works, please accept as solution.

[EugeneNovikov]

Thank you, Rupak, very elegant indeed. Now I'm going to put in into my main schema bc that was a small test model. 🙂