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

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

AlwaysAGooner — Fri, 17 Nov 2023 16:05:06 GMT

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

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

Jihwan_Kim — Sat, 18 Nov 2023 05:14:35 GMT

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

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

ThxAlot — Sat, 18 Nov 2023 07:02:24 GMT

Easy enough

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

Anonymous — Mon, 20 Nov 2023 07:04:44 GMT

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.

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

AlwaysAGooner — Tue, 21 Nov 2023 06:26:33 GMT

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

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

AlwaysAGooner — Tue, 28 Nov 2023 14:30:46 GMT

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

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

AlwaysAGooner — Tue, 28 Nov 2023 15:24:27 GMT

Anonymous

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

AlwaysAGooner — Wed, 29 Nov 2023 09:00:33 GMT

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

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

AlwaysAGooner — Wed, 29 Nov 2023 13:53:47 GMT

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 🙂