Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JensHN
Frequent Visitor

Select jobs based on their history

Hi,

 

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

 

connection.jpg

 

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.

 

jobs-table.jpghistory-table.jpg

 

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.

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

jobhistory.jpg

View solution in original post

2 REPLIES 2
v-cherch-msft
Employee
Employee

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

jobhistory.jpg

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.