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 am relativelly new to DAX and I'm trying to figure out filtering of duplicate values. I have data (Qty) for more materials (PN=Part numbers) for different dates. there can be more items for one PN and one date. I need to calculate day by day how many items per PN are available - running total.
On top of that I need to recalculate that to a different unit imagine a box. Each PN can fit different qty to a box.
Lastly every PN belongs to a family and the aim is to know how many boxes will be occupied per family each day.
Movements:
Planned_date | End date | PN | Index | Data1 | Datax | Qty | M1 | M2=M1/UM |
28.09.2022 | 03.10.2022 | A | 1 | 10 | 10 | 2 | ||
28.09.2022 | 29.09.2022 | B | 7 | 100 | 100 | 50 | ||
30.09.2022 | 04.10.2022 | B | 8 | 6 | 106 | 53 | ||
04.10.2022 | 05.10.2022 | A | 2 | 20 | 30 | 6 | ||
05.10.2022 | 05.10.2022 | A | 3 | -5 | 25 | 5 | ||
05.10.2022 | 05.10.2022 | A | 4 | -3 | 22 | 4,4 | ||
05.10.2022 | 05.10.2022 | A | 5 | 7 | 15 | 3 | ||
05.10.2022 | 09.10.2022 | B | 9 | -50 | 56 | 28 | ||
06.10.2022 | 31.12.2024 | A | 6 | 10 | 25 | 5 | ||
10.10.2022 | 31.12.2024 | B | 10 | 16 | 72 | 36 |
Units | UM |
A | 5 |
B | 2 |
Family | |
A | Family 1 |
B | Family 2 |
C | Family 1 |
D | Family 3 |
Target:
M3 | 28.09.2022 | 29.09.2022 | 30.09.2022 | 01.10.2022 | 02.10.2022 | 03.10.2022 | 04.10.2022 | 05.10.2022 | 06.10.2022 | 07.10.2022 | 08.10.2022 | 09.10.2022 | 10.10.2022 | 11.10.2022 |
A | 2 | 2 | 2 | 2 | 2 | 2 | 6 | 3 | 5 | 5 | 5 | 5 | 5 | 5 |
B | 50 | 50 | 53 | 53 | 53 | 53 | 53 | 28 | 28 | 28 | 28 | 28 | 36 | 36 |
Total | 52 | 52 | 55 | 55 | 55 | 55 | 59 | 31 | 33 | 33 | 33 | 33 | 41 | 41 |
I succesfully created a calculated column M1:
as well as measure M2
M2 = Calculate(SUMX(Movements,Movements[M1]/Related(Units[UM])))
but I am struggling with M3 with the duplicate lines (Index 3 & 4)
Solved! Go to Solution.
Hi @HCK ,
I created a sample pbix file(see attachment), please check if that is what you want.
If the above one can't help you get the desired result, please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi @HCK ,
I created a sample pbix file(see attachment), please check if that is what you want.
If the above one can't help you get the desired result, please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Thank you very much for your advice! You should me the logic to solve my issue. It seems that with a little adjustments of the columns and the measure I get to the required results! I want to verify on more data tomorrow and Accept your advice as a solution.
Thank you a lot!
So Finally I get to my sollution. The major breakthrough was your setup of the New Index column.
That was basically my initial wish:
"how to filterout rows based on two (or more) identical columns"
Answer: using your New index and then fitering the table by: NOT isblank
Details: I used the New Index to filterout my table using:
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |