Distinct count for the awards

[Switto]

Hi Team,

We are working on a report which provides us with the awards given from the learning perspective.

It has categories like White, Purple, Brown, Gold. We are trying to get the count of unique awards given to individual ID.

In the learning category, White is the lowest and Gold is the highest. Individuals can earn awards by completing courses.

However, while counting if one person awarded White as well as Purple in the same category then it will be counted as 1 as White will be not counted. But the person awarded White but no other awards then it will be counted as 1.

I have three column 

1) Name: It has Individual ID-ProgrammeName- SubProgrammeName

2) Color: White or Purple or  Browne or Gold

3) Award Status: Awarded or In Progress

Name	Color	Award Status	Count
123-AA-BBB	White	Awarded
123-AA-BBB	Brown	Awarded	1
234-AA-ZZZ	White	Awarded
234-AA-ZZZ	Brown	In progress
234-AA-ZZZ	Gold	Awarded	1
234-AA-AEW	White	Awarded	1
234-AA-AEW	Brown	In progress

Please let me know if we can achieve in PowerBI.

Thanks

S

[v-angzheng-msft · Accepted solution]

Hi, @Switto 

Try this:

Measure = 

VAR _nameTable =FILTER( ALL( 'Table' ), 'Table'[Name] = MAX( 'Table'[Name] ) )
VAR _addRank=
    ADDCOLUMNS(
        _nameTable,"rank",SWITCH(TRUE(),[Color] = "Gold", 1,[Color] = "Brown", 2,[Color] = "Purple", 3,[Color] = "White", 4)
    )
var _highest=MINX(_addRank,[rank])
var _countHighest=MINX(FILTER(_addRank,[Award Status]="Awarded"),[rank])

var _currentColor=MAX('Table'[Color])
var _currentRank=SWITCH(TRUE(),_currentColor = "Gold", 1,_currentColor = "Brown", 2,_currentColor= "Purple", 3,_currentColor = "White", 4)
RETURN IF(_currentRank=_countHighest,1,BLANK())

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[bcdobbs]

You could add a calculated column with combines Name and Color together eg 123-AA-BBB-White. It would be better to do that in PowerQuery so that the column gets compressed. At that point you can use DISTINCTCOUNT in a measure.

Looking at your data though is it possible for someone to have more than one row for the same award? If it isn't then you could just use COUNTROWS ( TableName ) in a measure.