Best way to exclude a list of values from a column

[punksterz626]

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

[Jihwan_Kim · Accepted 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" } )
)

[Jihwan_Kim · Accepted 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" } )
)

[punksterz626]

Thank you, it works. Question, how can i list the results in a matrix table format? Is it possible?

[Jihwan_Kim · Accepted solution]

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.

[punksterz626]

Thank you, that's exactly what i wanted. Much appreciated.