Count number of users with certain badges

[jxg104]

Hey,

I am looking to produce a count of the number of users in a community with a certain skill levels. The is as follows:

Name	Badge
John	Beginner
John	Intermediate
John	Advanced
Helen	Beginner
Helen	Intermediate
Jane	Beginner
Jane	Intermediate
Jane	Advanced
Jane	Power User
Brian	Beginner
David	Beginner
David	Power User

I would like to make the following counts in a Card on a dashboard

1. Number of users who have Beginner and Intermediate and ... (but not ONLY those values, they could have more too)
2. Number of users who have Beginner and Intermediate, but not Advanced.

Since the card is static, I would like to code the Badge into the query and not use a slicer. 

I have had a look round the forum about Basket Analysis and using double pipes, but I can't find a solution that uses the right syntax without using a slicer.

Many thanks!

[Anonymous]

Check out the attached file.

Best

D

[ibarrau · Accepted solution]

Hi, you can build measures like this:

Measure 1 =
CALCULATE (
    DISTINCTCOUNT(Table[Name]), 
    FILTER(
        SUMMARIZECOLUMNS(
            Table[Name],
            "@beginner", CALCULATE(COUNT(Table[Name]),Table[Badge]="Beginner"),
            "@intermediate", CALCULATE(COUNT(Table[Name]),Table[Badge]="Intermediate")
        ),
        [@beginner] + [@intermediate] = 2
    )
)

This case will check if they have both beginner and intermediate without caring if they have advance or not.

Now let's check the other measure:

Measure 2 =
CALCULATE (
    DISTINCTCOUNT(Table[Name]), 
    FILTER(
        SUMMARIZECOLUMNS(
            Table[Name],
            "@beginner", CALCULATE(COUNT(Table[Name])Table[Badge]="Beginner"),
            "@intermediate", CALCULATE(COUNT(Table[Name]),Table[Badge]="Intermediate"),
            "@advanced", CALCULATE(COUNT(Table[Name]),Table[Badge]="Advanced")
        ),
        [@beginner] + [@intermediate] + [@advanced] = 3
    )
)

This second way will check they are beginner and intermediate but NOT advanced.
You have to avoid interactions between filters and the card with this measure in order to work.

Hope this helps,

[Anonymous]

The measure with SUMMARIZECOLUMNS is wrong. SUMMARIZECOLUMNS must not be use in measures since the function does not recognize context transition. It can only be safely used in queries.

Best
D

[jxg104]

Amazing, thanks! This helps a lot! As a follow-up question, how could a filter to look at a combination of (for example) 4 badges? Since the AND only allows 2 arguments? Thanks a lot!

[ibarrau]

Well you don't have to use AND () if you don't want to. You can add all you want like this:

FILTER(
    Table,
    Condition 1 && Condition 2 && ... && Condition N
)

 AND is a helpful function when you need to ask for two and want everything in an order, but it's not mandatory.

Check out the second code I have sent. It contains an "AND" and then &&. You can use AND ( AND ( AND ( .... ) ) ) or the symbol &&.

Regards

[jxg104]

When I apply these functions to the data, it keeps returning the message (Blank). If I only add one of the arguments (e.g. only Begineer), it counts fine, but with 2, it comes up blank. Could it be that the function would look for "Beginner" and "Intermediate" in the same cell? Not sure what I have done wrong...

[ibarrau]

Oh! I have a silly mistake sorry. You shouldn't use AND. You have to use OR haha. Like this:

Measure 1 =
CALCULATE (
    DISTINCTCOUNT(Table[Name]),
    FILTER(
        ALL(Table[Badge]),
        OR(Table[Badge] = "Beginner", Table[Badge]="Intermediate")
    )
)

The symbol for the OR are double pipes "||" instead of &&.

That's why it was null, a row can't be "Beginner" and  "Intermediate" at the same time haha.

Regards,

FirstPost EDITED to be with OR

[jxg104]

Thanks! But now I think the number is too high - with "OR"/"||", it is now showing the users who have either Beginner, or Intermediate, or Both...  For my needs I need to find the distinct count of users who have Beginner and Intermediate (but of course, those two values would be in different cells) 

🙂 

[ibarrau]

I see mi mistake. It's a tricky measure. Before I blow my mind with this let me ask. Do you have any other value rather than advanced, intermediate and beginner? and in order to be an advanced, do you have to be first intermediate and beginner?

I have to ask because if your answer is yes to both, then it should this simple:

Measure 2 =
CALCULATE (
    DISTINCTCOUNT(Table[Name]),
    Table[Badge] <> "Advanced"
    )
)

If the answer is no, we should analyze a bit more to prepare this in a different way.

Regards,