Forum Discussion
Using inactive relationships
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.
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.
3 Replies
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.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.
