Select jobs based on their history

[JensHN]

Hi,

my datamodel contains the two tables "Jobs" and "Histroy", connected by 1:n.

The "Jobs" table has a simple ID and Name column.

The "History" tbale has an ID, Job ID (based on Jobs table), Status (each status has a specific number) and a Timestamp when the job changed the status.

My problem is, how do I select jobs based on their history.

For example:

I want to know how many jobs went from status 1 to 3 without 2. In the sample data it´s only Job C.

How many jobs are in status 3 and have not been status 2 any time before. In the sample data it´s no Job.

[v-cherch-msft]

Hi @JensHN

You may create measures to get the values. For example:

NextStatus =
CALCULATE (
    MAX ( History[Status] ),
    FILTER (
        ALLEXCEPT ( History, History[Job ID] ),
        History[ID]
            = MAX ( History[ID] ) + 1
    )
)

Status1-3 = COUNTROWS(FILTER(History,History[Status]=1&&[NextStatus]=3)) //how many jobs went from status 1 to 3 without 2

HasStatus2 =
VAR a =
    MAXX ( FILTER ( ALL ( History ), History[Status] = 3 ), History[ID] )
VAR b =
    CALCULATETABLE (
        VALUES ( History[Status] ),
        FILTER ( History, History[ID] < a )
    )
RETURN
    IF ( 2 IN b, 1 )

InStatus3 =
VAR a =
    MAXX ( FILTER ( ALL ( History ), History[Status] = 3 ), History[ID] )
RETURN
    IF (
        [HasStatus2] = BLANK (),
        CALCULATE ( DISTINCTCOUNT ( Jobs[Name] ), FILTER ( History, History[ID] < a ) )
    )  //How many jobs are in status 3 and have not been status 2 any time before

Regards,

Cherie

[JensHN · Accepted solution]

Hi @v-cherch-msft

thank you for your fast replay.

Your shown solution works well for the two named szenarios. As always, the reality is unfortunately much more complex. The requirements to filter a specific history szenario can change daily. Creating a DAX formula is to complex for the enduser.

My idea is to create a calculated filed with the status numbers. On the visualisation the enduser can apply a simple "contains filter" on this field.

Jobhistory = CONCATENATEX(RELATEDTABLE(History); History[Status];)