DAX command - to group number of issues in students

[NYS]

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 

[v-zhangti · Accepted solution]

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.

[Fowmy]

@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