Conditional Relationship between Tables

[Spencer_Bye]

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 link for each work item and data such as Status, Date_Created, Date_Completed, etc. The other table, is a series of historic snap shots of high level metrics. There is a line for each day of the past year with columns such as Current_Open_Requests and Daily_Response_Time.

My goal is to be able to select a day from the historic table and see all work orders that were open at that time by comparing their Date_Created and Date_Closed to the selected date. Some form of many-to-many relationship seems like the best way to do this but I am fairly new to Power BI so I unsure how best to handle this.

[MattAllington]

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. 

[Spencer_Bye]

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.

[v-yiruan-msft · Accepted solution]

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