Distinct count of string column by max value of number column

[linoe]

I found some threads that sounded similar but were a lot more complex, and this seems like it should be straight forward but I'm just not getting it. I have a table with just two columns, Title and Status. Status is a number (0,1,2),and Title has duplicates. I'm trying to get the distinct count of titles grouped by the max value of status. I've created a measure to get the Max for each Title, and that seems to work, but when I try to get the count for each max I just end up with a total count for the value of 2, not for each individual value. In the end, I'm looking for the results to be 1,2,3 for the max values of 0,1,2 respectively.

Title	Status		Max
Task 1	0		1
Task 1	1		1
Task 3	1		1
Task 4	2		2
Task 5	1		2
Task 5	2		2
Task 8	1		2
Task 8	0		2
Task 9	0		0
Task 8	2		2

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[linoe]

Geez, I dont know how I didn't think to just group in the query editor, I've done it for other things but for some reason here I was trying to do it all on the report side. This was so simple.

[v-danhe-msft]

Hi @linoe,

Due to I could not achieve your measure of [Max] column, I have used a sample data to replace, you could refer to below formula to count the value of [Max]:

Measure = CALCULATE(COUNT(Table1[Max]),ALLEXCEPT(Table1,Table1[Max]))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He