Group Table data together showing changes

[APS_SW]

This is the raw data I have to work with; and I do not have access to the original queries, but it is build from a local model using a direct Query to OneLake Data Hub, so I can add Messures, Calculated Columns, and indeed separate tables.

What I want to do, is have a much *much* shorter table where I can build off Visuals and only contains:

Date Status Changed, New Status

I've gone literally in circles with EARLIER() the idea behind it was if I could check the previous row which also had the same jobnumber and give a 1 or a 0 I could literally filter out the changes.

The only success I have had was going this method: https://community.fabric.microsoft.com/t5/Desktop/get-value-from-Previous-row/td-p/441336 every otehr attempt ended in errors (mostly circular dependancy)  but the linked method whilst it did not throw any errors I was not able to view the results... I suspect as it is massively memory intensive and the above table has 1.2 million rows.

Any thoughts on the best/easiest way to do this which does not involve adjusting the underlying data?

[Anonymous]

Hi @APS_SW,

You can try to use the following measure formula check the last change date to return flag based on current 'job number' and 'task status' group:

formula =
VAR currDate =
    MAX ( Table1[TaskStatusDate] )
VAR _lastDate =
    CALCULATE (
        MAX ( Table1[TaskStatusDate] ),
        ALLSELECTED ( Table1 ),
        VALUES ( Table1[jobnumber] ),
        VALUES ( Table1[TaskStatus] )
    )
RETURN
    IF ( currDate = _lastDate, 1, 0 )

Regards,

Xiaoxin Sheng

[APS_SW]

Thank you that is *almost* correct but only takes the MAX (date) of the status as the final and doesn't take into account mulltiple instances of the status changing.

I'll be investigating this today but anyone know of a quick fix?

[Anonymous]

HI @APS_SW,

My expression required the 'job number' and 'task status' as category fields, you can add them to the visual to help formula get correct result and display the each status last change date based on job number and task status.

Regards,

Xiaoxin Sheng

[APS_SW]

Thank you @Anonymous but as shown on the previous screenshot filters on the visuals do not help.
I have made some progress however, adding a index column based on category

NEW_IndexCat = 
VAR JN = Fact_Task[JobNumber]
VAR NT = FILTER( Fact_Task, Fact_Task[JobNumber] = JN)
RETURN RANKX( NT, Fact_Task[TaskStatusDate],,ASC)

And now I'm attempting to use it to pull in the previous value, although right now it returns a (blank)

NEW_Previous Status = 
SUMX(
    FILTER (
        Fact_Task,
        Fact_Task[JobNumber] = Fact_Task[JobNumber]
        && Fact_Task[NEW_IndexCat] = Fact_Task[NEW_IndexCat]-1
    ),
    Fact_Task[TaskStatusSk]
)

 
The issue I believe is in the line:

&& Fact_Task[NEW_IndexCat] = Fact_Task[NEW_IndexCat]-1

Changing it to an absolute value returns only the single line; I highly suspect I am trying to do things incorrectly.

I'll update this thread if thigns change but any advice or examples / fixes that would make my life easier will be much appriciated.

[APS_SW · Accepted solution]

Figured it out, using the NEW_IndexCat (as above)

NEW_Offset = 
MAXX(FILTER(ALL(Fact_Task), Fact_Task[JobNumber] = EARLIER(Fact_Task[JobNumber]) && Fact_Task[NEW_IndexCat] +1 = EARLIER(Fact_Task[NEW_IndexCat]) ), Fact_Task[TaskStatusSk] )