topic DAX command - to group number of issues in students in DAX Commands and Tips

DAX command - to group number of issues in students

NYS — Wed, 27 Dec 2023 13:50:42 GMT

Hi,

Thank you so much for helping. I'm in a university as an advisory faculty to student issues.

Here's the data I loaded into Power BI:

Table 1

Name	Issues
David	Academic
David	Psychosocial
David	Financial
Betty	Academic
Betty	Pyschosocial
John	Academic
John	Financial
Rachel	Psychosocial
Rachel	Professional
Fred	Academic
Cindy	Academic
George	Psychosocial
Ella	Financial
Simon	Professional

I have no problems doing this:

Issues	Number of Students
Academic	5
Psychosocial	4
Financial	3
Professional	2

What I did was to create a measure: Count = COUNTROWS(Table1)

Then I selected Table in Visualisation, clicked on Issues and the new measure Count (like the graphic below) and it returns the table above:

But what I really want to do is to construct a Table 2 from Table 1 to count how many students have how many issues: like this:

Table 2

Number of Issues	Number of Students
3	1
2	3
1	5

What measures or columns in DAX do I create so that I can click them to get the table 2 visualization?

Thank you so much! This is driving me mad!

YS, Ng

Re: DAX command - to group number of issues in students

Fowmy — Wed, 27 Dec 2023 14:06:39 GMT

NYS

Create a new table to get the sequence to of count of students.

Table2 = SELECTCOLUMNS( GENERATESERIES( 1 , DISTINCTCOUNT( Table1[Name] ) ), "Student Count" , [Value] )

Create the collowing meaure, using it on a table visual with the previous table column.

Issue Count = VAR __Std = SELECTEDVALUE( Table2[Student Count] ) RETURN COUNTROWS( FILTER( ADDCOLUMNS( SUMMARIZE( Table1 , Table1[Name] ), "Count" , CALCULATE( COUNT( Table1[Issues] ) ) ), [Count] = __Std ) )

File is attached

Re: DAX command - to group number of issues in students

v-zhangti — Thu, 28 Dec 2023 07:52:37 GMT

Hi, NYS

You can try the following methods.

Table:

Table2 = SUMMARIZE('Table1','Table1'[Name],"Count Name",CALCULATE(COUNT('Table1'[Name]),ALLEXCEPT('Table1','Table1'[Name])))

Column:

Number of Students = CALCULATE(COUNT(Table2[Count Name]),ALLEXCEPT(Table2,Table2[Count Name]))

Number of Issues = CALCULATE(COUNT('Table1'[Issues]),FILTER('Table1',[Name]=EARLIER(Table2[Name])))

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.