DAX to Count IDs that have gained on their previous scores

[AlwaysAGooner]

Hi,

I have a need to identify IDs in my data who have gone forwards on scoring if interviewed more than once. Set of IDs are evaluated for a Brand and based on responses a scoring value is assigned.

There could be a scenario where some IDs have scored more than what they had scored previously, i.e. I scored 2 in Apr'23 and Aug I scored 3.
All this (DAX Measure) must be responsive to date slicer, so the counting should happen only within the selected date range.

So, to summarize, the requirement is:

1. Identify Ids using a measure that have scored better than what they scored previously (have upgraded). One such case highlighted in the image, should only calculate for cases where an ID has been evaluated more than once

Any suggestion/leads will be greatly appreciated.

[AlwaysAGooner]

Changed it to CountX, works alright.

Stupendous work mate, many thanks

[FreemanZ]

hi @AlwaysAGooner ,

Not sure if i fully get you, try to plot a measure like:

Measure = 
VAR _table =
ADDCOLUMNS(
    VALUES(data[brand]),
    "LastDateScore",
    VAR _lastdate = CALCULATE(MAX(data[date]))
    RETURN
    CALCULATE(
        MAX(data[score]),
        data[date] = _lastdate        
    ),
    "2ndLastDateScore",
    VAR _lastdate = CALCULATE(MAX(data[date]))
    VAR _2ndlastdate =
    CALCULATE(
        MAX(data[date]),
        data[date] < _lastdate
    )
    RETURN
    CALCULATE(
        MAX(data[score]),
        data[date] =  _2ndlastdate
    )
)
VAR _list = 
CALCULATETABLE(
    VALUES(data[brand]),
    FILTER(
        _table,
        [LastDateScore]>[2ndLastDateScore]
            &&[2ndLastDateScore]<>BLANK()
    )
)
RETURN CONCATENATEX(_list, data[brand], ", ")

it worked like:

[AlwaysAGooner]

Thanks. I just need the count not the actuals. So while looking at Brand A for Q1 2023, how many picked on their previous scores.

Pls suggest.

[FreemanZ · Accepted solution]

try like:

Measure = 
VAR _table =
ADDCOLUMNS(
    VALUES(data[brand]),
    "LastDateScore",
    VAR _lastdate = CALCULATE(MAX(data[date]))
    RETURN
    CALCULATE(
        MAX(data[score]),
        data[date] = _lastdate        
    ),
    "2ndLastDateScore",
    VAR _lastdate = CALCULATE(MAX(data[date]))
    VAR _2ndlastdate =
    CALCULATE(
        MAX(data[date]),
        data[date] < _lastdate
    )
    RETURN
    CALCULATE(
        MAX(data[score]),
        data[date] =  _2ndlastdate
    )
)
RETURN 
COUNTROWS(
    FILTER(
        _table,
        [LastDateScore]>[2ndLastDateScore]
            &&[2ndLastDateScore]<>BLANK()
    )
)