Issue with Line Chart from 2 different tables

[CROforce]

I have a table called “observation” and amongst other things, I have “Date Opened”, “Date Closed” and RTMs”

What I am trying to do is to count how many items where open based on “Date Opened” but to filter out everything that is not RTM 1 and how many items where closed based on Closed Date and to filter everything out that is not RTM 1. Once completed, I wanted to present that data in Line Chart. Therfore, I have created new table with custom dates and index. Dates range from 9/30/15 – Today().

Then I have created relationship between ‘observation’ Date Opened and ‘open’ Date Open. I have created Measure as:

Measure RTM 1 Open = 
COUNTX (
    FILTER (
        'observation',
        'observation'[Date Opened] = RELATED ( 'Open'[Date Open] )
		&& ( 'observation'[RTMs] = "RTM 1" )
    ),
    'observation'[Date Opened]
)

Then I have created column: RTM 1 Open = [Measure RTM 1 Open] to show me the data

I have created one more measure that would be aggregating over time to show Net open:

Measure RTM 1 Net OPEN = 
    CALCULATE (
        SUM ( 'Open'[RTM 1 Open] ) ,
        FILTER ( ALL ( 'Open' ), 'Open' [Index] <= MAX( ( 'Open' [Index] ) )
    ))

And to represent that data I have created column RTM 1 Net Open = [Measure RTM 1 Net OPEN]

Now since Date Closed is different than Date open, I have created new table called “Closed” with custom dates (same as already explained above).

I have also created relationship between ‘observation’ Date Closed and ‘Closed’ Date Closed. I have created Measure as:

Measure RTM 1 Closed = 
COUNTX (
    FILTER (
        'observation',
        'observation'[Date Closed] = RELATED ( 'Closed'[Date Closed] )
		&& ( 'observation'[RTMs] = "RTM 1" )
    ),
    'observation'[Date Closed]
)

Then I have created column: RTM 1 Closed = [Measure RTM 1 Closed] to show me the data

I have created one more measure that would be aggregating over time to show Net closed:

Measure RTM 1 Net CLOSED = 
    CALCULATE (
        SUM ( 'CLOSED'[RTM 1 Closed] ) ,
        FILTER ( ALL ( 'CLOSED' ), 'CLOSED'[Index] <= MAX( ( 'CLOSED'[Index] ) )
    ))

And to represent that data I have created column RTM 1 Net CLOSED = [Measure RTM 1 Net CLOSED]

Now the issue is how do I present this data into the Line chart?

When I drop down “Date Open” from table “Open” and insert “RTM 1 Net Open” data looks perfectly fine… When I add “RTM 1 Net Closed”, data for closed issue would be not right and this is because (in my opinion) there is no relationship between tables “Open” and “Closed” and I am unable to create active relationship between the two because both of them already have relationship with “observation” table... Does anyone have a pointer how to solve this issue? It has been bugging me the whole day today and I just can't figure it out 😞

[CROforce · Accepted solution]

I have resolved this issue by creating one data table instead of multiple (one for Closed and one for Open)