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.
Dears,
I am new to BI and I have a small challenge:
I have 3 tables: Sales, QTY and Mapping:
Sales: QTY: Mapping:
Item| Sales Item| QTY Item| Item2| Date
A | 50 D | 53 A | D | 27/5/2017
B | 71 E | 67 A | Z | 29/5/2017
C | 85 Z | 70 A | X | 26/5/2017
X | 100
Action: I'd like to create a new table that takes items from sales and get the corresponding QTY from the QTY table based on latest mapping in the Mapping table. For example, A sales = 50 but QTY = 70 = Z because Z have the latest mapping of A.
How to implement such table?
Thanks!
Solved! Go to Solution.
Hi,
Maybe you didn’t create relationships among the tables. That could be why the sales is empty. Do the relationships look like this below?
If so, you can use these two measures to get “item2” and “sales”.
Item2Measure =
CALCULATE ( VALUES ( Mapping[item2] ), LASTDATE ( Mapping[date] ) )
QTYMeasure =
CALCULATE ( VALUES ( Qty[qty] ), LASTDATE ( Mapping[date] ) )
Are you trying to do this in DAX or M or do you care?
Can you please share both if possible and the difference between both in terms of performance and easiness to implement? Thanks!
Hi, @Omega
If you want to use DAX, this formula will help.
QTYMeasure =
CALCULATE ( VALUES ( Qty[qty] ), LASTDATE ( Mapping[date] ) )
Best Regards
Dale
Instead of getting the QTY from QTY table, how to get item 2 from mapping table? Meaning, I'll have two columns, one that have A and the other have Z.
Thanks!
Hi,
Did this work? What else can I do for you?
Best Regards!
Dale
Hi,
Maybe you didn’t create relationships among the tables. That could be why the sales is empty. Do the relationships look like this below?
If so, you can use these two measures to get “item2” and “sales”.
Item2Measure =
CALCULATE ( VALUES ( Mapping[item2] ), LASTDATE ( Mapping[date] ) )
QTYMeasure =
CALCULATE ( VALUES ( Qty[qty] ), LASTDATE ( Mapping[date] ) )
Thanks for the reply. I'll try to implement and let you know if I'm having challenges. 🙂
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 |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |