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 3 tables, Sales, Store and DistributionCenter. And these are my tables schemas em relationships:
Sales | Store | DistributionCenter | ||
storeID | ----> | storeID | ||
distributionCenterID | storeName | distributionCenterName | ||
specialCode | distributionCenterID | ----> | distributionCenterID |
And these are my tables data:
Sales | ||
storeID | distributionCenterID | specialCode |
1 | 1 | 1 |
1 | 1 | 1 |
1 | 1 | 2 |
1 | 1 | 2 |
2 | 2 | 1 |
2 | 2 | 2 |
2 | 2 | 2 |
Store | ||
storeID | storeName | distributionCenterID |
1 | StoreFoo | 1 |
2 | StoreBar | 2 |
DistributionCenter | |
distributionCenterID | distributionCenterName |
1 | Foo |
2 | Bar |
3 | Special |
So, the Sales tables is connected with stores' tables and the table Stores is connected with the table DistributionCenter. The problem is if the value of specialCode column on Sales table is 2, then the right distribution center is Special and the storeID can not be changed.
For example, a sale with specialCode as 1 and storeID as 1, points to distribution center 1 (Foo). But if the specialCode is 2, then it must points to distribuition center 3 (Special). Is it possible to accomplish that without changing the relationships?
Thanks.
Solved! Go to Solution.
@Anonymous we can try this by adding new column in sales table:
Dist Name = var d= RELATED(Dist[distributionCenterName]) return if(Sales[specialCode]=2, LOOKUPVALUE(Dist[distributionCenterName], Dist[distributionCenterID], 3), d)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
You should add a calculated column in your sales table for distributioncenterid and then link this new column directly from sales table to distributioncenter table.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k, thanks for helping me. I known that I can do that. But I do not want to change my relationships. So is it possible to do it without changing it?
@Anonymous we can try this by adding new column in sales table:
Dist Name = var d= RELATED(Dist[distributionCenterName]) return if(Sales[specialCode]=2, LOOKUPVALUE(Dist[distributionCenterName], Dist[distributionCenterID], 3), d)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |