DAX; Replace filter; Currency Converversion

[sdjensen]

Hi,

I am creating a PBI model on top of Dynamics NAV sales data. The model can handle multiple companies and I am currently adding currency conversion. But like the latest Apple presentations I am just missing "One last thing".

In my model I have added a currency fact table containing one row for each company, currency, date and currency exchange rate in the data. There is also a sales fact and a company, reporting currency and date dimension tables with relationships.

In the reporting currency dimension I have added a hidden column (DefaultCurrency) and set the value of this column to 1 for EUR and 0 for all other currencies (Currently the model has Local Currency, EUR and USD). The reason for this column is to assist the users of the report. So in case that no reporting currency is selected or if the user accidentically selected 2 reporting currencies the measure should return the value in EUR.

This is all working perfectly excect in one situation - If I set a filter for LCY and USD the measure returns (BLANK). I understand why this happens, but I am not sure how to solve it. This happens because the EUR currency is filtered from the model, hence there is no currency with the value 1 in DefaultCurrency. So in this case I should remove the filter from my Reporting Currency dimension and set a new filter - but how?

This is my current code to my measure:

Revenue = 
IF(
	HASONEVALUE( ReportingCurrency[Currency] );
	SUMX(
		CROSSJOIN( Company; Date; ReportingCurrency );
		CALCULATE( 
			DIVIDE( SUM(Sales[Revenue]); VALUES( CurrencyExchRate[CurrencyExchRate] ) )
		)
	);
	SUMX(
		CROSSJOIN( Company; Date; ReportingCurrency );
		CALCULATE(
			DIVIDE( SUM(Sales[Revenue]); VALUES( CurrencyExchRate[CurrencyExchRate] ) );
			ReportingCurrency[DefaultCurrency] = 1
		)
	)
)

[v-qiuyu-msft]

Hi @sdjensen,

Would you please share some sample data and report design? So that we can understand the issue better.

Best Regards,
Qiuyun Yu

[sdjensen]

@v-qiuyu-msft - I can try, but my models is build in danish, so not sure you will understand much of it. There is more fact and dimension tables in the model, but they don't affect my calculation, hence I didn't mention them all in my initial post.

This is my "ReportingCurrency" dimension table (in my model called Rapporteringsvaluta). With this dimension the user should select which currency they want to see in their reports. Unfortunately there is not option in PBI to set a dimension to single select only and to set a default value, hence I have to build in this logic in ALL the value measures. ValutaKode (CurrencyCode) is my Key column. I could in my measure just as well set a static default measure to be EUR by using ValutaKode = "EUR", but if I decide to change the default reporting currency to USD og Local I would have to change all the measures. Hence I have the column DefaultValuta (DefaultCurrency), so I can change the default currency for all measures by just moving the 1 value to another member.

This is my "Company" Dimension (in my model called Regnskab). It's really straight forward it has a Key value used for relationships and a value for the user.

This is my Date dimension (in my model called Periode). Also a very straight forward date dimension table with loads of value. Dato is my Key value used for relationships.

Below is my CurrencyExchRate fact table (in my model called Valutakurser). It holds a CurrencyExchRate (Valutakurs) for each Company (RegnskabKey), Currency (ValutaKode) and Date (ValutaDato).

This is my relationship between the mentioned tables. For these tables I only use many:1 relationships.

My Measure is called Revenue (in my model called Salg Omsætning) has this formula which is the same as the one on my previous model just with the danish names instead.

Salg Omsætning = 
IF(
	HASONEVALUE( Rapporteringsvaluta[Valuta] );
	SUMX(
		CROSSJOIN( Regnskab; Periode; Rapporteringsvaluta );
		CALCULATE( 
			DIVIDE( SUM(Salg[SalgOmsætning]); VALUES( Valutakurser[Valutakurs] ) )
		)
	);
	SUMX(
		CROSSJOIN( Regnskab; Periode; Rapporteringsvaluta );
		CALCULATE(
			DIVIDE( SUM(Salg[SalgOmsætning]); VALUES( Valutakurser[Valutakurs] ) );
			Rapporteringsvaluta[DefaultValuta] = 1
		)
	)
)

This is the result of my measure. Split by Year, Quarter and Month (on rows) and Company, Reporting Currency on Columns.

This is the same table without the Reporting Currency dimension on the Columns and without any filter on Reporting Currency which I added to Report level filters. As you can see it shows my value in EUR because this is the member in the Reporting Currency dimension where DefaultCurrency is set to 1.

If I select just one currency in Report Level Filter there is no issue with my measure is works. If I select RV and EUR (result is shown in EUR) or EUR and USD (result is shown in EUR) the measure also works because the cube can still see the member in the Reporting Currency dimension where Default Value is 1. But if I select RV and USD the measure doesn't return anything and what I want is it to return my value in EUR.

[sdjensen]

Doesn't anyone have an idea on how to solve this? I played around with FILTER, ALL, ALLEXCEPT, but no matter how I combine these I can't get what I want.