Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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];)
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
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];)
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |