Countrows for specific values (where more values are possible)

[Anonymous]

Hi,

I am working on a report where I need to make a count of people (persons) that only are using 2 specific roles. However, a person can be using more roles and I am only interested in those 2 "unique" ones.

Example:

Person 1 / roles: A/B/D/E

Person 2 / roles: A/B

Person 3 / roles: B

Person 4 / roles: A

Person 5 / roles: B/C/E

Person 6 / roles: A/D/E/F

Person 7 / roles: A/C/E/F

Which approach / function in DAX do I need to persue (use) to get the number of all persons with A or B only (so people with A/B + others needs to be excluded, in this example person 1,5,6,7).

[Whitewater100]

Hi:

You can use a calc column:

A B Roles = IF(OR(OR('Table'[Roles] = "A/B",'Table'[Roles] = "A"),'Table'[Roles] = "B"), 1,BLANK())

I hope this helps!

[Anonymous]

Hi, Thanks a lot for the quick response. Sounds like an intersting idea. However in the result (table) I also get users with both e.g. role A and D. Any idea why? So I need very explicit, based on a long list with persons, to filter out all those with more roles that A and B.

[Whitewater100]

Hi:

If you only want A or B and not A/B combined, then you can use this calculated column.

A B Roles Only = IF(OR('Table'[Roles] = "A",'Table'[Roles] = "B"), 1,BLANK())

I'll paste image below. Can you please mark as solved? Thanks...

[Anonymous]

Hi,

Result seems to be the same, I still get users with other roles (activities) in the list, it does not seem to produce a list with the As or Bs only. I can close it, but maybe someone else knows a trick for this?

[Whitewater100]

Hello:

Please show some example data. Maybe you have special characters or something else in your data?

An example file would help as the two ways used to ID A&B are very commonplace.

Thanks!

[Anonymous · Accepted solution]

So I am actually using it know, I am new to this, so .... but I am using it the other way around (I have turned the roles around). Thanks a lot.