Forum Discussion
Group Table data together showing changes
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?
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] )
5 Replies
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
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?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
