Forum Discussion
Solved
Conditional Relationship between Tables
Currently, I am working with a data set that tracks work order requests. The data is stored in two seperate tables. One is the current status of all of our work orders past and present. It includes a...
Hi Spencer_Bye ,
You can follow the steps below to get it:
1. Create a measure as below
Flag = VAR _hisdate = SELECTEDVALUE ( 'HistoricTable'[Date] ) VAR _wknum1 = SELECTEDVALUE ( 'CurrentStatusTable'[WorkOrder Number] ) VAR _wknum2 = CALCULATE ( MAX ( 'CurrentStatusTable'[WorkOrder Number] ), FILTER ( 'CurrentStatusTable', 'CurrentStatusTable'[WorkOrder Number] = _wknum1 && 'CurrentStatusTable'[Date_Created] <= _hisdate && ( ISBLANK ( 'CurrentStatusTable'[Date_Completed] ) || 'CurrentStatusTable'[Date_Completed] >= _hisdate ) ) ) RETURN IF ( ISBLANK ( _wknum2 ), 0, 1 )2. Apply a visual-level filter on your matrix visual with the condition (Flag is 1)
Best Regards
I think I have gotten your suggested solution to work. I have been able to create a matrix that shows the work order number and a status based on the date selected. However, I can not seem to filter the matrix by the measure. Currently, I have to scroll through all the tickets to see which ones are marked Active and which ones are marked Complete.
Hi Spencer_Bye ,
You can follow the steps below to get it:
1. Create a measure as below
Flag =
VAR _hisdate =
SELECTEDVALUE ( 'HistoricTable'[Date] )
VAR _wknum1 =
SELECTEDVALUE ( 'CurrentStatusTable'[WorkOrder Number] )
VAR _wknum2 =
CALCULATE (
MAX ( 'CurrentStatusTable'[WorkOrder Number] ),
FILTER (
'CurrentStatusTable',
'CurrentStatusTable'[WorkOrder Number] = _wknum1
&& 'CurrentStatusTable'[Date_Created] <= _hisdate
&& (
ISBLANK ( 'CurrentStatusTable'[Date_Completed] )
|| 'CurrentStatusTable'[Date_Completed] >= _hisdate
)
)
)
RETURN
IF ( ISBLANK ( _wknum2 ), 0, 1 )2. Apply a visual-level filter on your matrix visual with the condition (Flag is 1)
Best Regards