topic Re: DAX to Count IDs that have gained on their previous scores in DAX Commands and Tips

DAX to Count IDs that have gained on their previous scores

AlwaysAGooner — Thu, 30 Nov 2023 05:45:31 GMT

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.

Re: DAX to Count IDs that have gained on their previous scores

FreemanZ — Thu, 30 Nov 2023 07:17:56 GMT

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:

Re: DAX to Count IDs that have gained on their previous scores

AlwaysAGooner — Thu, 30 Nov 2023 07:23:08 GMT

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.

Re: DAX to Count IDs that have gained on their previous scores

FreemanZ — Thu, 30 Nov 2023 07:32:02 GMT

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() ) )

Re: DAX to Count IDs that have gained on their previous scores

AlwaysAGooner — Thu, 30 Nov 2023 07:49:24 GMT

Changed it to CountX, works alright.

Stupendous work mate, many thanks