Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bsz412
Helper III
Helper III

select column dynamically in dax measure - is it possible?

Hi, 

 

In my report I have a separate start and end filter for selecting time period.

bsz412_0-1660211445990.png

I have to calculate sellout amount to EUR, by using a sellout column in sales table and the exchange rate from the exchange rate table.

The tricky part is, that if Aug 22 is selected, they want everything to be calculated with Aug 22 ex rate. If Jul 22 is selected as end period, they want everything to be calculated on that exchange rate.

 

First I had an ex rate table like this: 

bsz412_1-1660211571470.png

and it was connected with many to many relationship to the sales table by the country code column. 

Sales amount was calculated with the help of a lookup function. The disadvantage of the Lookup function is the performance.  RELATED function would be much faster, but for that I would need a one to many relationship.

Now, what I am trying to do, is that I modified the exchange rate table, pivoted it, so that I have the ex rates in different columns and I can now establish the one to many relationship to the sales table.

bsz412_2-1660211740832.png

I can use now the RELATED function, but as each month is in a different column, I would have to add each and every column in the code, in the switch function. (The code always have to use the exchange rate of the selected end period)

 

Is it possible to reference the column name with a measure, instead of adding 50-60 lines of code,  each month one by one? It should always use the column name that is identical with the selected end period. 

Thank you!

Sellout Amount EUR test = 

VAR ex_ratedate = SELECTEDVALUE(Endperiod[yearmonth number])

RETURN

SUMX(

sales, sales[sellout]*

SWITCH(

TRUE(),

ex_ratedate=202101, RELATED(exchange_rate[202101]),

ex_ratedate=202102, RELATED(exchange_rate[202102]),

RELATED(exchange_rate[202206])))
1 REPLY 1
DataInsights
Super User
Super User

@bsz412,

 

See if you can adapt the solution below. It uses a bridge table to solve the many-to-many relationship. In your model, CountryCode will be the bridge table. This approach allows you to avoid pivoting the exchange rate table.

 

https://community.powerbi.com/t5/Desktop/Alternative-LookupValue-function-to-create-virtual-relation... 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.