Count the Number of Systems selected by User

[Anonymous]

I have a data set that I need to perform a count of systems selected by a user (either 1, 2 or 3).  I then will take that result and do a count of the number of users that picked 1 system, 2 system or 3 systems.  Data is structured as follows:

User	System	System Selected
User1	Secondary Business System	No
User1	Tertiary Business System	No
User1	Primary Business System	No
User1	Unlisted Business Systems	No
User2	Primary Business System	Yes
User2	Secondary Business System	No
User2	Tertiary Business System	No
User2	Unlisted Business Systems	No
User3	Secondary Business System	Yes
User3	Primary Business System	Yes
User3	Unlisted Business Systems	No
User3	Tertiary Business System	No
User4	Secondary Business System	Yes
User4	Unlisted Business Systems	No
User4	Tertiary Business System	No
User4	Primary Business System	Yes
user5	Unlisted Business Systems	No
user5	Secondary Business System	Yes
user5	Primary Business System	Yes
user5	Tertiary Business System	Yes
user6	Tertiary Business System	No
user6	Primary Business System	No
user6	Unlisted Business Systems	No
user6	Secondary Business System	No

And the resulting set by user should be

User	Total Systems Selected
User1	0
User2	1
User3	2
User4	2
user5	3
user6	0

and then the final system count would look like

Total Systems Selected	# Users
0	2
1	1
2	2
3	1

I know it's a count function, but I'm just not quite sure how to approach it in power bi.  

[mahoneypat · Accepted solution]

Try this measure, sustituting Table with your actual table name for your first table (with User column).

UserSelectedCount = Calculate(DistinctCount(Table[System]), Table[System Selected]="Yes")

For the second table, you'll need another table that has the 0,1,2,3.  You can generate a DAX table with

Counts = GENERATESERIES(0, 3, 1)

No relationship to any other table needed.

You can then make a table with the Counts[Values] column and this measure.

SystemCount = var selectedcount = Selectedvalue(Counts[Value])

return Countrows(Filter(Values(Table[User]), [UserSelectedCount] = selectedcount))

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mahoneypat · Accepted solution]

Try this measure, sustituting Table with your actual table name for your first table (with User column).

UserSelectedCount = Calculate(DistinctCount(Table[System]), Table[System Selected]="Yes")

For the second table, you'll need another table that has the 0,1,2,3.  You can generate a DAX table with

Counts = GENERATESERIES(0, 3, 1)

No relationship to any other table needed.

You can then make a table with the Counts[Values] column and this measure.

SystemCount = var selectedcount = Selectedvalue(Counts[Value])

return Countrows(Filter(Values(Table[User]), [UserSelectedCount] = selectedcount))

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

I was able to create the measure and the count table but the second table is giving me an error.  I used the syntax:

SystemCount = var selectedcount = Selectedvalue(Counts[Value])

to create the DAX table, but it is saying the syntax for ')' is incorrect.  I may be misunderstanding that last step.  I feel like it's close I just need a little clarification on the last part.

[v-xuding-msft · Accepted solution]

Hi  @Anonymous ,

I test the method that @mahoneypat suggested. My sample works fine. You just used the wrong formula.

The second table:

Counts = GENERATESERIES(0, 3, 1)

 

The measure:

SystemCount = 
var selectedcount = Selectedvalue(Counts[Value])
return 
Countrows(Filter(Values('Table'[User]), [UserSelectedCount] = selectedcount))