Forum Discussion

Switto's avatar
Switto
Icon for Helper IV rankHelper 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 un...
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.

bcdobbs's avatar
bcdobbs
Icon for Community Champion rankCommunity 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.