Filter Matrix using inactive relationship

[Lee5150]

Hi all,

Apologies if this has been covered elsewhere, but I am unable to find what I need.
I thought this would work but I am unable to use it effectively.

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Matrix-table-filter-by-inactive-rela...

I have the following data table.

I also have 3 matrix visuals that show the Type column and the Start, Installed, and Completed columns, respectively.

Also a slicer that shows the months of the year.

When I select a month from the slicer I'd like each matrix to show the dates in their respective date columns, that match the slicer. 

The relationship between the slicer and the data table is current set to 'Start Date', which obviously filters all 3 matrix visuals to that. 

Any ideas how I can achieve what I need? I've tried allsorts, using inactive relationships etc. 

I just don't have enough knowledge to make it work!

Many thanks in advance!

[V-yubandi-msft]

Hi @Lee5150 ,

We haven’t received a response yet and want to ensure the solution met your needs. If you need any further assistance, feel free to reach out we’d be happy to help. If everything is working as expected, kindly mark it as Accepted as solution.

Thank You.

[V-yubandi-msft]

Hi @Lee5150 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Lee5150]

Many thanks! I just need help formulating the measures to get each matrix to display the correct info. Nothing I have tried has worked so far. 

[Lee5150]

Hi, does any one else have an idea for this? Or does any one know what I might be doing wrong with the previous answer?

Many thanks

[V-yubandi-msft]

Hi @Lee5150 ,

The issue likely persists because USERELATIONSHIP requires correct inactive relationships and no conflicting filters.

Please try.

1. Set Calendar[Date] to Completed Date (active), and to Start Date and Installed Date (inactive) in the Model  view.

2. If it still only shows Completed Date, ensure no global or visual-level filters are overriding the measures (check the Filters pane).

 I hope this helps....

[Lee5150]

Hi, and thank you again!

I still can't get this working. I started a new project and set everything up from scratch, and it's still not working for me. 

Please see the we transfer link to my file. For some reason, I was unable to attach it.

WeTransfer Link 

Please could you tell me what I've done wrong?

Thank you again!

[V-yubandi-msft]

Hi @Lee5150 ,

Thank you for sharing.

1. Only the relationship between DateTable[Date] and Completed Date is active.

2. Relationships with Start Date and Installed Date are inactive but still present.

3. Create separate measures using the USERELATIONSHIP function. Use these measures in your three separate Matrix visuals.

4. Each visual should reference only its corresponding measure. The month slicer will now work independently for each matrix visual, as each one uses its own relationship logic via the measure.

[Lee5150]

Hi again, does anyone have any further info on how I can achieve this? I'm really struggling to formulate the measures correctly.

Many thanks

[V-yubandi-msft · Accepted solution]

Hi @Lee5150 ,

Apologies for the delay in response. The behavior you’re seeing is inherent to Power BI, as it processes data in this manner by default. However, I’ve adjusted the relationships based on your specific requirements by duplicating the necessary elements. While this approach may not be fully recommended in a practical scenario, it aligns with your needs.

I’ve attached the PBIX file please review it, and you should get a clearer idea of the adjustments made. Let me know if you need further refinements.

I hope this helps....

[Lee5150]

Hi, many thanks for your reply! This looks like exactly what I need. However, I'm struggling to formulate a DAX expression that works! 
Any chance you can help me write this correctly. I've researched USERELATIONSHIP but I cannot get it to work correctly.

TIA

[HarishKM]

@Lee5150 Hey,
I have created a calender table using below dax.

calender =

ADDCOLUMNS(CALENDAR(DATE(2025,1,1),DATE(2025,05,31)),

"Year",YEAR([Date]),

"Month",MONTH([Date]),

"Month- Year ",FORMAT([Date],"MMM-YY"))

you can update date as per your requirement.

Modeling
I have created a active relationship with

I have created active relationship to completed date. then I am using calender month year for this.

Kindly try this. hopefully this will solve your issue.

Thanks

Harish 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Lee5150]

Hi, and many thanks for your reply.

I can't get this solution to work. I can see that your model works exactly like it should, but mine looks like this.

It's still only taking the completed dates into account.

I have made the relationship between completed date and the date column in the calendar table, and marked the other 2 relationships as inactive.

[HarishKM]

@Lee5150 Hey,
You do not need to create bi directional cross filtering in your report and you can follow below logic.
I am using a calendar 

Calender = ADDCOLUMNS(CALENDAR(DATE(2000,1,1),DATE(2060,12,31)),

"year",YEAR([Date]),

"Month", MONTH([Date]),

"Month-year",FORMAT([Date],"MMM-YY"),

"Qtr",QUARTER([Date]),

"QTR-YY","Q"&"-"&FORMAT([Date],"YY"),

"IsWorkingDay",

        IF (

            WEEKDAY([Date], 2) <= 5,  // 2 = Monday as the first day of the week (1 = Sunday, 7 = Saturday)

            "Yes",

            "No"

        )

    )

I have create a active relationship with start date

I have inactive relation with other date then i am getting result as below.

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query.

[Lee5150]

Thank you for this, although this is the result I am getting, but it's not result I need.
I can see all 3 of your matrixes are filtering using the start date column. On your example, I would need all 3 matrixes to display rows showing April 2022 in their respective date columns.