Forum Discussion
Conditional Relationship between Tables
- Anonymous2 years ago
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 suggest you load a calendar table and link it, one to many, to the history table using the date of the extract. You click the day of interest and then the history table will be filtered by that single extract. You then need to write some DAX (as a measure) that will return a non blank result if the work order is open and a blank result if it is closed. Such a measure could, for example, return "Active" or BLANK(). Add this measure to a matrix with the work order number and it will give you what you want.
writing this measure can be tricky. I suggest you load some sample data into an Excel spreadsheet with the open date, close date, and extract date. Then write the logic as a column in an excel table. Once you sort out that logic, you can replicate it in DAX. Pseudo code would be something like... If the extract date is on or after the open date and also one or before the close date, then it's currently active.
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.
- Anonymous2 years agoNot applicable
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