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.
I have a report that calculates the turnover per article. Some articles have been received a new article number but for reporting purposes I want to show the turnover combined with the old article. There is a dimension table with the relationship between the old and the new article number but I am trying to find the best way to use this in my calculations.
Dimension table 1 : article
Article number | Article name |
1 | Product 1 |
2 | Product 2 |
3 | Product 3 |
Dimension table 2: old article number new article number
Article number | New Article number |
1 | 3 |
Sales table
Article number | Sales Amount |
1 | 10 |
2 | 15 |
3 | 20 |
1 | 15 |
3 | 10 |
I need the Sales to show a turnover of 55 on article 3 and 0 on article 1. How do I override the existing relationship I have between the dimension table and the fact table. I would really appreciate the help.
Solved! Go to Solution.
This is how I would do it,
Creating a new column in both Sales and Article Table whihc would have the overridden article value.
I have used LOOKUPVALUE for the same
Then creating a relation ship between these two tables base don overridden article number
and then visualise it as normal. Refer attached sample file
The only downside to this is the many to many relationship that it forms that you can avoid using a bridge table.
Thank you amitchandak, I tried your formula but I could not make this check : Dim2[Article number] = Sales[Article number] in my dax code so something seems to be missing?
This is how I would do it,
Creating a new column in both Sales and Article Table whihc would have the overridden article value.
I have used LOOKUPVALUE for the same
Then creating a relation ship between these two tables base don overridden article number
and then visualise it as normal. Refer attached sample file
The only downside to this is the many to many relationship that it forms that you can avoid using a bridge table.
Thank you PC2790, your solution works. I was able to avoid the many to many relationship by using the function USERELATIONSHIP.
@Binkhorst , Create a new column in sales Table
New Ids =
var _1 = maxx(filter(Dim2, Dim2[Article number] = Sales[Article number]), Dim2[New Article number])
return
if(isblank(_1) , [Article number] , _1)
Join this with dimesion
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |