Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Countrows for specific values (where more values are possible)

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).

  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

6 Replies

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Whitewater100
        Icon for Solution Sage rankSolution Sage
        4 years ago

        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...