topic Re: DAX - Selected slicer/value and row in data column are same, then give a certain output in DAX Commands and Tips

DAX - Selected slicer/value and row in data column are same, then give a certain output

Anonymous — Mon, 08 Jul 2019 14:29:00 GMT

Hi all,

I'm very new to Dax and I'm building a model in PowerPivot (which I will use in PowerBi when finished).

I have a financial report where I'm converting multiple currencies to one reporting currency which is selected by a slicer. It is set up as in the following link:

https://www.kasperonbi.com/currency-conversion-in-dax-for-power-bi-and-ssas/

I have an Exchange Rate table set up. However for each day I am missing the currency rate where the "From currency" is the same as the "to currency" => So where my Exchange rate needs to be 1.

I thought of adding lines into my table but this would mean adding thousands of lines for multiple currencies on multiple dates.

I thought of creating a dax formula where I can say => If the row in the facts table (can be any currency) is the same the reported (selected with slicer) currency, then I want the amount in the row to be multiplied by 1. Otherwise I want the row to be multiplied by the actual exchange rate. (see a pivot example below where the issue occurs when EUR to EUR is reported)

My current measures are like this :

TrnExchangeRate=MIN('Dim Exchange rates'[Exch. Rate])

This will get the lowest factor value from the fact table.

Now to calculate the sales per transaction I will use SUMX to achieve this:

TotalSales = =IF(HASONEVALUE(ReportCurrency[ReportCurrency]);SUMX('Fact PL Data';'Fact PL Data'[Amount in local currency]*[TrnExchangeRate]))

However if my currency in PL DATA is "EUR" for example, and my reporting currency is also "EUR", then I currently get a blank value. Because I never have 1 as an exchange rate in my table.

What would be the easiest dax formula to solve this?

Or would you recommend me adding all these in the exchange rate table for each day?

Re: DAX - Selected slicer/value and row in data column are same, then give a certain output

Anonymous — Mon, 08 Jul 2019 14:45:10 GMT

I was googling around in the meantime and thought I could do it like this:

=if(ISBLANK('Dim Exchange rates'[Min of Exch. Rate]);1;MIN('Dim Exchange rates'[Exch. Rate]))

But I think this is not best practice. Because if I will have some dates where I actually do not have any exchange rate data between different currencies, then I will probably get 1 as an exchange rate, instead of an actual blank...

Re: DAX - Selected slicer/value and row in data column are same, then give a certain output

Anonymous — Mon, 08 Jul 2019 15:17:42 GMT

With this comes another issue

=> I checked my Exchange rate data and I am actually missing exchange rates for some transactions =>

For example CZK to CHF currency I do not have any rates for some dates.

Could there be an easy workaround for this? Like taking the average of a previous period instead of having a blank calculation? Or going from CZK to EUR and then finding the EUR/CHF rate? But my Dax knowledge is super limited so no idea how to set this up....