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