# Second Variant of Scoring after the Third Occurrence

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

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

punch.xlsx (9.1 KB)
thanks Brian

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

1. 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?

2. 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)?

3. Nonconsecutive unscheduled - each incident counts as a 1, regardless of whether it’s the 1st, 4th or 10th incident?

Thanks for clarifying.

• Brian

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

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

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

Bumping this post for more visibility.

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

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

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

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] )
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] )
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 =
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] )
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 =
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 =
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.

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

2 Likes

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!

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.

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

1 Like

Hi @lizbethl4, bumping this post again.

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.

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

BB

Hello @lizbethl4 following up on this. Thanks!

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