Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
AlwaysAGooner
Helper I
Helper I

DAX to Count IDs that have gained on their previous scores

 
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

 

AlwaysAGooner_1-1701323079787.png

 

 

Any suggestion/leads will be greatly appreciated.

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

View solution in original post

4 REPLIES 4
AlwaysAGooner
Helper I
Helper I

Changed it to CountX, works alright.

Stupendous work mate, many thanks

FreemanZ
Super User
Super User

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:

FreemanZ_0-1701328532640.png

FreemanZ_1-1701328560305.png

FreemanZ_2-1701328597474.png

 

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.

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors