Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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];)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |