Using inactive relationships

[maurcoll]

Good Morning
i have two tables an open table and a closed table for individual orders. Each table has the same ID column for the order number and a month year column and the product type and a total.

I have a date table also.

I have joined the two tables on the ID field. I have joined to the date table using month year with one as an active and one as an inactive relationship.

The issue i am having is that i can not filter using slicers on both the month year and product type in the visuals. Is there a way of doing this. 

For example i want to know in May 22 how many orders for Product A were opened and how many were closed.

[danextian]

Hi @maurcoll 

As always, please provide a usable sample data (not an image) that complete covers your use case and your expected result using that sample data rather than make anyone who'd want to help imagine how your data looks.

[maurcoll]

Orders Opened
ID	Month Year	Product	Total
1 -A	Jan-22	A	12
2-A	Jan-22	A	10
3-B	Feb-22	B	5
4-A	Feb-22	A	4
5-B	Jan-22	B	3
6-C	Jan-22	C	15
Orders Closed
ID	Month Year	Product	Total
1-A	Jan-22	A	12
3-B	Feb-22	B	5
4-A	Mar-22	A	4
5-B	Feb-22	B	3

What i want to  be able to do is see how many orders were opened in Jan and how many closed in jan and what the percentage is. I want to be able to filter on product and Date. Orders may not be closed in the month they were opened.
The tables are joined on ID and to a date table using Month Year with open as an active relationship and closed as an inactive relationship.

[danextian · Accepted solution]

Hi @maurcoll 

Based to the provided data, there's no necessity to usean inactive relationship unless there are multiple relationships between the same tables (eg closed and opened dates are in the same row but different columns in the same table). In your scenario, the tables are distinct from each other.  I would use the data model below:

This should allow you to includ the three dimensions in one table with data for opened and closed orders:

Please also see attached pbix for your reference.