Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi ,
Hope this would be simple doubt. But I need help to crack the solution in Power bi
Have Two tables:
Table 1: Same part # stock received on muliple dates / Unique Ref.
Table 2: Usage of the parts without any reference
Output Needed :
1. Each line in Table 1 should have a new coulum with the Qty used, which should not be greater than the received qty
2. The Qty considereded for the previous line from table should be deducted for the next line calculation
Table 1 | ||
Unique Ref ID | Part # | Received Qty |
ID1 | A | 2000 |
ID2 | A | 2000 |
ID3 | A | 2000 |
ID4 | A | 2000 |
ID5 | B | 2500 |
Table 2 | |
Part # | Qty Used |
A | 7000 |
B | 5000 |
OutPut Expected | |||
Unique Ref ID | Part # | Qty | Qty Used (Table 2) |
ID1 | A | 2000 | 2000 |
ID2 | A | 2000 | 2000 |
ID3 | A | 2000 | 2000 |
ID4 | A | 2000 | 1000 |
ID5 | B | 2500 | 2500 |
Thanks
Mak
Solved! Go to Solution.
Try like new columns
Col1 = sumx(filter(table1, table1[Part #]=earlier(table1[Part #]) && table1[Unique Ref ID]<=earlier(Unique Ref ID])),table[Received Qty])
col2 = sumx(filter(table2, table1[Part #]=table2[Part #]),table2[Qty Used])
col3 = if((col2-col1)<col2,(col2-col1),col2)
Appreciate your Kudos.
Try like new columns
Col1 = sumx(filter(table1, table1[Part #]=earlier(table1[Part #]) && table1[Unique Ref ID]<=earlier(Unique Ref ID])),table[Received Qty])
col2 = sumx(filter(table2, table1[Part #]=table2[Part #]),table2[Qty Used])
col3 = if((col2-col1)<col2,(col2-col1),col2)
Appreciate your Kudos.
Hi Amit,
It works. Thanks for the solution.
Regards,
Mak
First, can you guarantee that Unique Ref ID will always sort alphabetically such that the earliest "instances" are always alphabetically "less" than later "instances". If that cannot be guaranteed, do you have a date column or index column where you can distinguish "earlier" from "later"?
Because if you don't, this will be difficult if not impossible.
Hi Greg,
Yes , the unique ref ID are sequenced numeric data which can be sorted from earliest. In that case what solution can support in this case.
Thanks,
Magesh
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
92 | |
82 | |
63 | |
62 | |
58 |
User | Count |
---|---|
159 | |
115 | |
103 | |
75 | |
66 |