Forum Discussion

Switto's avatar
Switto
Helper IV
4 years ago
Solved

Distinct count for the awards

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

 

NameColorAward StatusCount
123-AA-BBBWhiteAwarded 
123-AA-BBBBrownAwarded1
234-AA-ZZZWhiteAwarded 
234-AA-ZZZBrownIn progress 
234-AA-ZZZGoldAwarded1
234-AA-AEWWhiteAwarded1
234-AA-AEWBrownIn progress 

 

Please let me know if we can achieve in PowerBI.

Thanks

S

 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

2 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    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.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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.