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 All,
I'm inquiring about the best way to exclude a list of values from a column when trying to count rows. These numeric values are unique. I am trying to count how many jobs are in my list, but certain ones do not meet the criteria and I wish to exclude them. I have a list of over 30 jobs ID, but I have reduced the amount here for the simplicity of this exercise. This list will likely be ongoing and will likely be additional jobs added in the future. What is the best way to write a measure to exclude them?
Example: I have a list of 16 jobs listed below and I want to exclude 57329, 70252, and 40506. Now my total should be 13 jobs. What is the best way to achieve this?
CategoryJob Number
A | 44313 |
A | 17168 |
A | 73348 |
A | 57329 |
A | 42925 |
A | 00548 |
A | 40506 |
A | 49690 |
A | 52855 |
A | 66307 |
A | 70252 |
A | 96697 |
A | 70842 |
A | 00954 |
A | 96694 |
A | 99503 |
Solved! Go to Solution.
Hi,
I assume the Job number Column is TEXT type.
Please try to write the measure like below, and also please check the attached pbix file.
Total jobs count under condition: =
CALCULATE (
COUNTROWS ( VALUES ( Data[Job number] ) ),
NOT ( Data[Job number] IN { "57329", "70252", "40506" } )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Thank you for your message, and I am not sure how you want to show it.
Please check the attached file if it works for you.
Thank you.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I assume the Job number Column is TEXT type.
Please try to write the measure like below, and also please check the attached pbix file.
Total jobs count under condition: =
CALCULATE (
COUNTROWS ( VALUES ( Data[Job number] ) ),
NOT ( Data[Job number] IN { "57329", "70252", "40506" } )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you, it works. Question, how can i list the results in a matrix table format? Is it possible?
Hi,
Thank you for your message, and I am not sure how you want to show it.
Please check the attached file if it works for you.
Thank you.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you, that's exactly what i wanted. Much appreciated.
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 |
---|---|
109 | |
101 | |
86 | |
77 | |
69 |
User | Count |
---|---|
120 | |
108 | |
98 | |
83 | |
77 |