topic Stuck with DAX-ing slicer selection in DAX Commands and Tips

Stuck with DAX-ing slicer selection

EugeneNovikov — Thu, 20 Feb 2025 22:28:05 GMT

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.

Re: Stuck with DAX-ing slicer selection

Rupak_bi — Fri, 21 Feb 2025 08:31:26 GMT

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.

Re: Stuck with DAX-ing slicer selection

EugeneNovikov — Fri, 21 Feb 2025 09:29:41 GMT

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