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.
Looking for a DAX formula to remove rows based on duplicate serial numbers. I currently have a data table with multiple rows. Some of the rows have the same serial number. I would like to count the sum the rows, but remove rows that have the same serial number. The formula I have currently sums up all rows. Parts Per Year = sum('Table'[column])
Please help in letting me know how to modify this so it only sums/counts rows that a unique serial number. Serial number is its own column in the same data table.
Solved! Go to Solution.
Oops!
Got it,
Tt_SerialNumber_Replace = CALCULATE(
DISTINCTCOUNT(Planilha1[serial Number]);
filter(ALL(Planilha1);Planilha1[Part Replace]=1))
Please try and test using it. And please mark the right answer as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
Best Regards,
Rfranca
Thanks that helped, but still having trouble. The distinctcount showed how many rows had a unique serial number, but would like to count the number of rows with a unique serial number when a true/false condition is met in a different columnt on the table.
Sample data below, looking for the dax formula so the rows that are counted or summed equals 5. Only count the serial number if part replace equals 1 and only count the serial number one time.
serial Number | Part Replace |
1 | 1 |
1 | 1 |
1 | 0 |
1 | 1 |
2 | 1 |
2 | 1 |
3 | 1 |
4 | 0 |
4 | 0 |
4 | 1 |
5 | 1 |
6 | 0 |
6 | 0 |
Oops!
Got it,
Tt_SerialNumber_Replace = CALCULATE(
DISTINCTCOUNT(Planilha1[serial Number]);
filter(ALL(Planilha1);Planilha1[Part Replace]=1))
Please try and test using it. And please mark the right answer as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
Best Regards,
Rfranca
Measure = CALCULATE(DISTINCTCOUNT('Table'[serial Number]),FILTER('Table','Table'[Part Replace]=1))
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 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |