DAX to Count IDs that have not retained on their previous scores

[AlwaysAGooner]

Hi,

I have a need to identify IDs in my data who have gone backwards on scoring and those who have retained their scores if interviewd more than once. Set of IDs (Individuals) are evaluted for a Brand and based on respnses a scoring value is assigned.

There could be a scenario where some Individuals have scored less than what they had scored previously, i.e. I scored 3 on in Apr'23 and Aug I scored 2. And then there would be case of those who have retained their scores.
All his must be responsive to date slicer, so the counting should happen only within the selected date range.

So to summarize, the requirements are:

1. Identify Ids that have scored than what they scored previously (have degraged). One such case highlighted in the image
2. Identify Ids that have retained their score

Any suggestion/leads will be greatly appreciated

[AlwaysAGooner]

Hi,

The code works fine. May I ask help again to also calulate 'Upgraded'? Count of those ids who have mutiple entries and have gained on the score since last evaluated, i.e. Current Score > Previous Score.

I tried making the operator change in the 'Degraded' code but that takes into account those ids as well who have been evaluted just once.

Thanks

[Anonymous]

Hi @AlwaysAGooner ,

Please try code as below to create measures.

IDs Retained Score = 
VAR _SCORELIST =
    SUMMARIZE (
        'Table',
        'Table'[ID],
        "Count",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Score] ),
                ALLEXCEPT ( 'Table', DimDate[Date], 'Table'[ID] )
            )
    )
RETURN
    COUNTX ( FILTER ( _SCORELIST, [Count] = 1 ), [ID] )

Count Degraged = 
VAR _SCORELIST =
    SUMMARIZE (
        'Table',
        'Table'[ID],
        "Current",
            CALCULATE (
                SUM ( 'Table'[Score] ),
                FILTER ( 'Table', 'Table'[Date] = MAX ( 'Table'[Date] ) )
            ),
        "Previous",
            CALCULATE (
                SUM ( 'Table'[Score] ),
                FILTER (
                    'Table',
                    'Table'[Date]
                        = MAXX (
                            FILTER (
                                ALLEXCEPT ( 'Table', 'Table'[ID], DimDate[Date] ),
                                'Table'[Date] < MAX ( 'Table'[Date] )
                            ),
                            'Table'[Date]
                        )
                )
            )
    )
RETURN
    COUNTAX ( FILTER ( _SCORELIST, [Current] < [Previous] ), [ID] )

Result is as below.

Best Regards,
Rico Zhou

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

[AlwaysAGooner]

Hi,

The code works fine. May I ask your help again to also calulate 'Upgraded'? Count of those ids who have mutiple entries and have gained on the score since last evaluated, i.e. Current Score > Previous Score.

I tried making the operator change in the Degraedd code but that takes into account those ids as well who have been evaluted just once.

Thanks

[AlwaysAGooner]

@Anonymous 

[ThxAlot]

Easy enough

[AlwaysAGooner]

Need it as a measure that would return Unquie count if IDs

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

Score measure: = 
SUM( Data[Score] )

Condition measure: = 
VAR _prevscore =
    CALCULATE (
        [Score measure:],
        OFFSET (
            -1,
            SUMMARIZE ( ALL ( Data ), Brand[ID], Brand[Brand], 'Calendar'[Date] ),
            ORDERBY ( 'Calendar'[Date], ASC ),
            ,
            PARTITIONBY ( Brand[Brand], Brand[ID] )
        )
    )
VAR _condition =
    IF (
        NOT ISBLANK ( [Score measure:] ),
        IF ( [Score measure:] < _prevscore, -1, 0 )
    )
RETURN
    _condition

[AlwaysAGooner]

Thanks for putting in efforts. I need a measure that will give me count of IDs within the selected date range. Also, do not want to create another table to use PartitionBy.

Pls suggest some other solution. Thanks 🙂