Sequence calculated column

[Anonymous]

Hey everyone,

I have a calculated column, called IndexSequenceGroup, but it needs to be tweaked a little bit. 

The formula is as follows: 
IndexSequenceGroup =
IF('Table'[IndexSequence] = 0 ,
IF (
ISBLANK (
MINX (
FILTER (
ALL ( 'Table' ),
'Table'[EmployeeId]
= EARLIER ( 'Table'[EmployeeId] )
&& 'Table'[IndexSequence]
<> EARLIER ( 'Table'[IndexSequence] )
&& 'Table'[ID]
> EARLIER ( 'Table'[ID] )
),
'Table'[ID]
)
),
1000,
MINX (
FILTER (
ALL ( 'Table' ),
'Table'[EmployeeId]
= EARLIER ( 'Table'[EmployeeId] )
&& 'Table'[IndexSequence]
<> EARLIER ( 'Table'[IndexSequence] )
&& 'Table'[ID]
> EARLIER ( 'Table'[ID] )
),
1000 + 'Table'[ID]
)
),
1000 - 'Table'[ID])

Can the formula be changed, so that I get the output like the data I added manually? 

[Anonymous]

Hi @Anonymous ,
I can see that you want to tweak the dax expression to achieve the result you want. The dax expressions you provide are really limited to the context, sometimes only the upper and lower lines. Can you explain the logic behind the desired outcome that you want to achieve? For example, what results need to be returned under what conditions.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi @Anonymous 

Yes of course!

The data is about employees being absent from work. I have a measure which calculates the frequency of the employee being absent. That measure is based on the column IndexSequenceGroup. But there should be made a small change. The column ‘Verschil’ counts the days between the ‘Start’ (the first day an employee is absent) and the previous row ‘Eind’ (the last day of the previous time the employee was absent). The condition is when ‘Verschil’ < 28, it is count that the employee is never fully returned. So that’s why the number for Employee 465457 with ID 4 should not be 1005, but 997. Because ‘Verschil’ is 3, hence less than 28. (And ‘IndexSequence’ is based on that column: it returns a 0 when ‘Verschil’ is blank or <28, otherwise a 1).

So for employee 508239, the frequency measure should give 2, where it no returns 3. Because the third time, on 25-01-2025, the employee is absent again, within 28 days. It should not return 1000, but 998. And all the following rows are within 28 days, so the number should stay the same.

Hopefully this explains enough context. Feel free to make a completely different formula. The output for example of 1002, 998, etc. is not relevant. It’s more about the sequence.

[Anonymous · Accepted solution]

@Anonymous 

it is solved! 

I created another calculated column, which allowed me to tweak the rows which needed the adjustment mentioned earlier. 

These are the final formulas: 

InterimStep =
IF('Table'[IndexSequence] = 0 ,
IF (
ISBLANK (
MINX (
FILTER (
ALL ( 'Table' ),
'Table'[EmployeeId]
= EARLIER ( 'Table'[EmployeeId] )
&& 'Table'[IndexSequence]
<> EARLIER ( 'Table'[IndexSequence] )
&& 'Table'[ID]
> EARLIER ( 'Table'[ID] )
),
'Table'[ID]
)
),
1000,
MINX (
FILTER (
ALL ( 'Table' ),
'Table'[EmployeeId]
= EARLIER ( 'Table'[EmployeeId] )
&& 'Table'[IndexSequence]
<> EARLIER ( 'Table'[IndexSequence] )
&& 'Table'[ID]
> EARLIER ( 'Table'[ID] )
),
1000 + 'Table'[ID]
)
),
1000 - 'Table'[ID])

IndexSequenceGroup =
VAR _PrevRow =
MINX(
FILTER('Table',
'Table'[EmployeeId] = EARLIER('Table'[EmployeeId]) &&
'Table'[ID] < EARLIER('Table'[ID]) &&
'Table'[IndexSequence] > EARLIER('Table'[IndexSequence])),
'Table'[InterimStep])

RETURN
IF(
_PrevRow = BLANK(),
'Table'[InterimStep],
_PrevRow)