topic Re: Count number of users with certain badges in DAX Commands and Tips

Count number of users with certain badges

jxg104 — Mon, 30 Mar 2020 11:13:47 GMT

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

Number of users who have Beginner and Intermediate and ... (but not ONLY those values, they could have more too)
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!

Re: Count number of users with certain badges

ibarrau — Mon, 30 Mar 2020 16:06:12 GMT

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,

Re: Count number of users with certain badges

jxg104 — Mon, 30 Mar 2020 14:05:42 GMT

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!

Re: Count number of users with certain badges

ibarrau — Mon, 30 Mar 2020 14:27:36 GMT

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

Re: Count number of users with certain badges

jxg104 — Mon, 30 Mar 2020 15:00:07 GMT

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

Re: Count number of users with certain badges

ibarrau — Mon, 30 Mar 2020 15:17:08 GMT

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

Re: Count number of users with certain badges

jxg104 — Mon, 30 Mar 2020 15:22:21 GMT

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)

🙂

Re: Count number of users with certain badges

Anonymous — Mon, 30 Mar 2020 15:33:24 GMT

Check out the attached file.

Best

Re: Count number of users with certain badges

ibarrau — Mon, 30 Mar 2020 15:36:33 GMT

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,

Re: Count number of users with certain badges

Anonymous — Tue, 31 Mar 2020 07:10:51 GMT

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