cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sdjensen Senior Member
Senior Member

DAX; Replace filter; Currency Converversion

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
		)
	)
)
/sdjensen
3 REPLIES 3
Moderator v-qiuyu-msft
Moderator

Re: DAX; Replace filter; Currency Converversion

Hi @sdjensen,

 

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

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sdjensen Senior Member
Senior Member

Re: DAX; Replace filter; Currency Converversion

 

@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.

ReportingCurrency_Dim.png

 

 

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.

Company_Dim.png

 

 

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.

Date_Dim.png

 

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).

CurrencyExchRate_Fact.png

 

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

Relationships.png

 

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.

Table_NoFilter.png

 

 

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.

Table_NoFilter2.png

 

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.

Table_FilterRV_USD.png

/sdjensen
sdjensen Senior Member
Senior Member

Re: DAX; Replace filter; Currency Converversion

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.

/sdjensen

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 244 members 2,942 guests
Please welcome our newest community members: