Second Variant of Scoring after the Third Occurrence

lizbethl4 · May 12, 2021, 8:29pm

Hi Brian and thank you. works well if EMPA has no other types of comments. when there are other types such as unscheduled consecutive and unscheduled nonconsecutive. when unscheduled consecutively 6/8/20,6/9/20 = 1 point, nonconsecutive 1 point, of course missed punch doesn’t have to be consecutive and you worked that one out already. again thank you. see below updated table

BrianJ · May 12, 2021, 8:45pm

@lizbethl4,

This is a very different requirement than previously stated, so I have marked the previous post as solved and broken this off into its own thread. If you could please provide the Excel file as a source, that would be helpful.

Thanks.

– Brian

lizbethl4 · May 12, 2021, 9:43pm

punch.xlsx (9.1 KB)
thanks Brian

BrianJ · May 12, 2021, 10:48pm

@lizbethl4,

I’ve read this over a bunch of times and am still a bit unclear on the business rules:

Missed punch - count doesn’t start until after the third missed punch. But does EACH missed punch after that get counted as 1, or does it just get a 1 if >=4 missed punches?
Consecutive unscheduled - let’s say that 6/10/20 was also an unscheduled event. Would that be counted as 1 (6/8-6/10) or 2 (6/8-6/9 + 6/9-6/10)?
Nonconsecutive unscheduled - each incident counts as a 1, regardless of whether it’s the 1st, 4th or 10th incident?

Thanks for clarifying.

Brian

lizbethl4 · May 12, 2021, 11:21pm

Thanks Brian

Missed punch: Yes missed punch doesn’t get counted until after 3rd punch

Consecutive unscheduled: counted as 1 6/8 to 6/10.
The rule is for unscheduled if consecutive counts as 1.

Nonconsecutive: each incidence counts as 1 ie
Unscheduled: 4/1/20, 4/8/20, 5/3/20 = 3

Thank you so much.

Liz

BrianJ · May 13, 2021, 3:56pm

@lizbethl4,

Thanks for the clarifications.

Missed punch - will be easy to modify my prior solution to address the new specs.

Nonconsecutive unscheduled - this one’s a breeze, just a traditional CALCULATE/COUNTROWS/FILTER combo.

Consecutive unscheduled - this one is a bear. I worked out a solution calculating winning streaks a while ago, and I think I can use many of the same concepts here. Probably won’t have time to dedicate to this one until late tonight, but will get you a solution as soon as I can.

Brian

lizbethl4 · May 13, 2021, 9:31pm

Hi Brian
consecutive unscheduled I have this one but couldn’t figure out how to incorporate it into your code for missed punches.
see below:
cummulative break = VAR vCurrentDate = TABLE[DATE]
VAR vCurrentId = TABLE[EMPID]
VAR vNextTbl = filter(TABLE, TABLE[DATE] = vCurrentDate + 1 && TABLE[EMPID] = vCurrentId)
RETURN
IF(COUNTROWS(vNextTbl) >0,0,1)

ROLLING 12 MTH OCCURENCES =

VAR vCurrentDate = table[DATE]

VAR _Ini = TABLE[cummulative break] - 1

VAR _StartDate = DATEADD(TABLE[DATE], - _Ini, DAY)

VAR vCurrentId = TABLE[EMPID]

VAR vCommentId = TABLE[COMMENTID]

VAR vNextTbl = FILTER(TABLE, TABLE[DATE] = vCurrentDate + 1 && TABLE[EMPID] = vCurrentId && TABLE[COMMENTID] = VCommentId)

VAR vRangeTbl = FILTER(TABLE, TABLE[DATE] >= _StartDate && TABLE[DATE] <= vCurrentDate && TABLE[EMPID] = vCurrentId )

VAR _Acum = CALCULATE(SUM(TABLE[Points]), ALL(TABLE), vRangeTbl)
RETURN
_Acum

EnterpriseDNA · May 16, 2021, 11:57pm

Bumping this post for more visibility.

BrianJ · May 17, 2021, 1:09am

@EnterpriseDNA,

Thanks for the bump. I will be working this one tonight – just had a couple of lengthy ones in the queue ahead of it.

– Brian

BrianJ · May 17, 2021, 7:20pm

@lizbethl4,

Turned my attention to this last night, but then at the last minute needed to focus on the video for today. Will have a solution for you later tonight…

Update - Sorry for the delay - worked on this for a long time last night. Haven’t quite cracked it yet but I think I’m close. Will continue today. Very interesting problem…

Brian

BrianJ · May 18, 2021, 6:17pm

@lizbethl4,

Okay, I’ve got the Missed Punch and Nonconsecutive Unscheduled code working properly. My question is whether Missed Punch, Nonconsecutive Unscheduled and Consecutive Unscheduled represent the full universe of possible outcomes? If so, will make the final measure much simpler because then it can basically be defined as the absence of the other two conditions.

Please let me know, and I should be able to finish up the solution for you shortly thereafter.

Thanks!

Brian

BrianJ · May 19, 2021, 7:15am

@lizbethl4,

Please check my results to confirm, but I believe I’ve cracked this one:

On the face of it, it looks pretty straightforward but actually has a ton of depth and complexity to it – really enjoyed working on this one with you. Here are the four key measures – one for each business rule and one to combine and total the points values according to the business rules:

Missed Punch Points = 

VAR PtsAfter3rd = 1
VAR First3 =
    CALCULATETABLE(
        TOPN( 3, 'Data', 'Data'[Date], 1 ),
        FILTER( ALL( 'Data' ), 'Data'[COMMENTID] = "63" )
    )
VAR DataMinusFirst3 =
    CALCULATETABLE(
        ADDCOLUMNS( EXCEPT( 'Data', First3 ), "@MPPoints", PtsAfter3rd ),
        'Data'[COMMENTID] = "63"
    )
VAR MaxDate3 =
    CALCULATE(
        MINX(
            FILTER( 'Data', 'Data'[DATE] >= MAXX( First3, 'Data'[DATE] ) ),
            'Data'[DATE]
        ),
        REMOVEFILTERS( 'Data'[DATE] )
    )
VAR Result =
    SWITCH(
        TRUE(),
        SELECTEDVALUE( 'Data'[COMMENTID] ) <> "63", BLANK(),
        SELECTEDVALUE( Data[DATE] ) <= MaxDate3, BLANK(),
        1
    )
VAR ResultTally =
    IF(
        HASONEVALUE( 'Data'[DATE] ),
        Result,
        SUMX( DataMinusFirst3, [@MPPoints] )
    )
RETURN
    Result

    Consec Unscheduled Points = 

    VAR CurrCommID =
        SELECTEDVALUE( 'Data'[COMMENTID] )
    VAR CurrDate =
        SELECTEDVALUE( 'Data'[DATE] )
    VAR CurrEmp =
        SELECTEDVALUE( 'Data'[EMPID] )
    VAR LastDataDate =
        CALCULATE(
            MAX( 'Data'[DATE] ),
            REMOVEFILTERS(
                'Data'[DATE],
                'Data'[COMMENTID]
            )
        )
    VAR PrevDate =
        CALCULATE(
            MAX( 'Data'[DATE] ),
            FILTER(
                ALL( 'Data' ),
                'Data'[DATE] < CurrDate
                    && 'Data'[EMPID] = CurrEmp
            )
        )
    VAR PrevCommID =
        CALCULATE(
            MAX( 'Data'[COMMENTID] ),
            FILTER(
                ALL( 'Data' ),
                'Data'[DATE] = PrevDate
                    && 'Data'[EMPID] = CurrEmp
            )
        )
    VAR NextDate =
        CALCULATE(
            MIN( 'Data'[DATE] ),
            FILTER(
                ALL( 'Data' ),
                'Data'[DATE] > CurrDate
                    && 'Data'[EMPID] = CurrEmp
            )
        )
    VAR NextCommID =
        CALCULATE(
            MAX( 'Data'[COMMENTID] ),
            FILTER(
                ALL( 'Data' ),
                'Data'[DATE] = NextDate
                    && 'Data'[EMPID] = CurrEmp
            )
        )
    VAR ConsecUnsched =
        IF(
            (
                PrevCommID = "54" &&
                NextCommID <> "54" &&
                CurrCommID = "54"
            ),
            1,
            BLANK()
        )
    RETURN
        ConsecUnsched

Nonconsec Unsched Points = 

VAR CurrCommID =
    SELECTEDVALUE( 'Data'[COMMENTID] )
VAR CurrDate =
    SELECTEDVALUE( 'Data'[DATE] )
VAR CurrEmp =
    SELECTEDVALUE( 'Data'[EMPID] )
VAR LastDataDate =
    CALCULATE(
        MAX( 'Data'[DATE] ),
        REMOVEFILTERS( 'Data'[DATE], 'Data'[COMMENTID] )
    )
VAR PrevDate =
    CALCULATE(
        MAX( 'Data'[DATE] ),
        FILTER( ALL( 'Data' ), 'Data'[DATE] < CurrDate && 'Data'[EMPID] = CurrEmp )
    )
VAR PrevCommID =
    CALCULATE(
        MAX( 'Data'[COMMENTID] ),
        FILTER( ALL( 'Data' ), 'Data'[DATE] = PrevDate && 'Data'[EMPID] = CurrEmp )
    )
VAR NextDate =
    CALCULATE(
        MIN( 'Data'[DATE] ),
        FILTER( ALL( 'Data' ), 'Data'[DATE] > CurrDate && 'Data'[EMPID] = CurrEmp )
    )
VAR NextCommID =
    CALCULATE(
        MAX( 'Data'[COMMENTID] ),
        FILTER( ALL( 'Data' ), 'Data'[DATE] = NextDate && 'Data'[EMPID] = CurrEmp )
    )
VAR Nonconsec =
    IF( OR( PrevCommID = "63", NextCommID = "63" ), BLANK(), 1 )
VAR BoundaryCond =
    IF(
        AND( SELECTEDVALUE( 'Data'[DATE] ) = LastDataDate, PrevCommID <> "54" ),
        1,
        BLANK()
    )
VAR NonconsecPts = Nonconsec + BoundaryCond
VAR DataWithPoints =
    ADDCOLUMNS(
        SUMMARIZE( ALL( 'Data' ), 'Data'[EMPID], 'Data'[DATE], 'Data'[COMMENTID] ),
        "@NCPoints", Nonconsecpts
    )
VAR Result =
    IF(
        HASONEVALUE( 'Data'[DATE] ),
        NonconsecPts,
        SUMX( DataWithPoints, [@NCPoints] )
    )
RETURN
    ResultNonconsec Unsched Points = VAR CurrCommID =
    SELECTEDVALUE( 'Data'[COMMENTID] )
VAR CurrDate =
    SELECTEDVALUE( 'Data'[DATE] )
VAR CurrEmp =
    SELECTEDVALUE( 'Data'[EMPID] )
VAR LastDataDate =
    CALCULATE(
        MAX( 'Data'[DATE] ),
        REMOVEFILTERS( 'Data'[DATE], 'Data'[COMMENTID] )
    )
VAR PrevDate =
    CALCULATE(
        MAX( 'Data'[DATE] ),
        FILTER( ALL( 'Data' ), 'Data'[DATE] < CurrDate && 'Data'[EMPID] = CurrEmp )
    )
VAR PrevCommID =
    CALCULATE(
        MAX( 'Data'[COMMENTID] ),
        FILTER( ALL( 'Data' ), 'Data'[DATE] = PrevDate && 'Data'[EMPID] = CurrEmp )
    )
VAR NextDate =
    CALCULATE(
        MIN( 'Data'[DATE] ),
        FILTER( ALL( 'Data' ), 'Data'[DATE] > CurrDate && 'Data'[EMPID] = CurrEmp )
    )
VAR NextCommID =
    CALCULATE(
        MAX( 'Data'[COMMENTID] ),
        FILTER( ALL( 'Data' ), 'Data'[DATE] = NextDate && 'Data'[EMPID] = CurrEmp )
    )
VAR Nonconsec =
    IF( OR( PrevCommID = "63", NextCommID = "63" ), BLANK(), 1 )
VAR BoundaryCond =
    IF(
        AND( SELECTEDVALUE( 'Data'[DATE] ) = LastDataDate, PrevCommID <> "54" ),
        1,
        BLANK()
    )
VAR NonconsecPts = Nonconsec + BoundaryCond
VAR DataWithPoints =
    ADDCOLUMNS(
        SUMMARIZE( ALL( 'Data' ), 'Data'[EMPID], 'Data'[DATE], 'Data'[COMMENTID] ),
        "@NCPoints", Nonconsecpts
    )
VAR Result =
    IF(
        HASONEVALUE( 'Data'[DATE] ),
        NonconsecPts,
        SUMX( DataWithPoints, [@NCPoints] )
    )
RETURN
    Result

Total Points = 

VAR vTable =
    ADDCOLUMNS(
        SUMMARIZE(
            'Data',
            'Data'[EMPID],
            'Data'[DATE],
            'Data'[COMMENTID]
        ),
        
        "@MP", [Missed Punch Points],
        "@Consec", [Consec Unscheduled Points],
        "@Nonconsec", [Nonconsec Unsched Points]
    )
    
VAR Result =
    IF(
        HASONEVALUE( 'Data'[DATE] ),
[Missed Punch Points] + [Consec Unscheduled Points] + [Nonconsec Unsched Points],
        SUMX(
            vTable,
            [@Consec]+[@Nonconsec]+[@MP]
        )
    )
RETURN
    Result

I hope this is helpful. Full solution file attached below.

Brian
eDNA Forum - Count after 3rd Occurrence 2nd Solution.pbix (96.7 KB)

P.S. I think this post set a personal record for me – solution took 165 lines of DAX!

EnterpriseDNA · May 20, 2021, 10:39pm

Hi @lizbethl4, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

EnterpriseDNA · May 24, 2021, 12:48am

Hi @lizbethl4, we’ve noticed that no response has been received from you since the 14th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

lizbethl4 · May 24, 2021, 5:20pm

Hi thanks, I’m testing today. will circle back this afternoon.
thanks.

EnterpriseDNA · May 27, 2021, 1:11am

Hi @lizbethl4, bumping this post again.

Did the response provided by @BrianJ help you solve your query?

EnterpriseDNA · May 28, 2021, 1:19am

Hi @lizbethl4, we’ve noticed that no response has been received from you since the 25th of May.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

lizbethl4 · May 28, 2021, 1:38am

Hi I’m working through some issues with the code.
I’ll send details .

BB

EnterpriseDNA · June 1, 2021, 12:55am

Hello @lizbethl4 following up on this. Thanks!

EnterpriseDNA · June 2, 2021, 11:32pm

Hi @lizbethl4, due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks