Forum Discussion

APS_SW's avatar
APS_SW
Regular Visitor
2 years ago
Solved

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, Calc...
Need Help
Tutorial Requests
  • APS_SW's avatar
    APS_SW
    2 years ago

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

     



APS_SW's avatar
APS_SW
Regular Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    APS_SW
    Regular Visitor
    2 years ago

    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's avatar
      APS_SW
      Regular Visitor
      2 years ago

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