Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a scenario where I have to divide 2 column, one in one table and the other in another table, which are linked by a Many-to-Many Relationship, and I was hopping to get some help from you Dax geniuses out there, please.
Quick explanation of the scenario : I have a transactions table, with sales, and these sales are in many different currencies. To convert them to a single currency, we have to divide the sales by the rate, which is in a second table rates.
There are 2 types of rates : an average rate per month, and an average rate per year. The link between the 2 tables is the period, and the currency. The relationship between the 2 tables is many to many ( Check model , please )
What I have tried so far: I could make it work, in 2 different ways, but none completely satisfying :
1 ) Dax expression with SumX :
Dividing the sum of sales by the sum of rates ( I must always have one selected rate type, so it's ok to sum the rates )
This works, but the performance is terrible. I have a transactions table with millions of records. Takes almost 1 minute to calculate YTD.
2 ) Forcing a many to 1 relationship :
If I apply some modeling to the tables, I can make it a 1 to Many relationship and create a calculated column, which will give a good performance. But that involves joining both tables, and it takes ages, even with Chris Webb's ideas, it will still take more than 1 hour to join the tables.
What I would like to know: Is there any other way of doing this? I'd really appreciate some ideas that I can test.
Simplified Model:
Sales:
Period | Currency | Sales | KeyColumn |
202001 | USD | 55 | 202001USD |
202001 | EUR | 13 | 202001EUR |
202001 | GBP | 7 | 202001GBP |
201904 | USD | 65 | 201904USD |
201904 | EUR | 27 | 201904EUR |
201904 | GBP | 96 | 201904GBP |
202001 | GBP | 33 | 202001GBP |
202001 | EUR | 44 | 202001EUR |
Rates:
RateType | KeyColumn | RateValue |
AvgMonthRate | 202001USD | 55 |
AvgMonthRate | 202001EUR | 13 |
AvgMonthRate | 202001GBP | 7 |
AvgMonthRate | 201904USD | 65 |
AvgMonthRate | 201904EUR | 27 |
AvgMonthRate | 201904GBP | 96 |
AvgAnnualRate | 201904USD | 55 |
AvgAnnualRate | 201904EUR | 13 |
AvgAnnualRate | 201904GBP | 7 |
AvgAnnualRate | 202001USD | 65 |
AvgAnnualRate | 202001EUR | 27 |
AvgAnnualRate | 202001GBP | 96 |
Expected Results:
Thanks in advance!
What if you keep your annual rates and your monthly rates in different tables?
hi @BIBen
I have tried that approach as well. But then you need a conditional measure and an extra table with a single column to calculate sales. Sometimes they add / remove rate types, so I'd like to keep it dynamic if possible.
I have good experience with data modeling, but I'm relatively new to power BI, so I'm just checking if there's some smart way of sorting this I'm not aware of.
If I'm doing the right things and it is what it is, I'll mark your answer as correct, thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |