Forum Discussion

Lee5150's avatar
Lee5150
Helper I
1 year ago
Solved

Filter Matrix using inactive relationship

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.co...
Need Help
  • V-yubandi-msft's avatar
    V-yubandi-msft
    1 year ago

    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....

     

     

HarishKM's avatar
HarishKM
Super User
1 year ago

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's avatar
    Lee5150
    Helper I
    1 year ago

    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's avatar
      HarishKM
      Super User
      1 year ago

      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's avatar
        Lee5150
        Helper I
        1 year ago

        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.