Forum Discussion
Single slicer, single data table, multiple matrix visuals not
I have a single data table for work orders. Each record has a created date and a closed date. I'm trying to create a dashboard with 3 visualizations (2 matrix, 1 table) that can be filtered by year/month. One matrix is to display those orders created in the selected year/month, one matrix is to display those orders closed in that year/month, and the table is to display those orders that are open yet not closed within that year/month.
I have created a Date Table and measures for createddate/closeddate/openorders. I have an active one:many relationship between the date table and created date, and an inactive relationship between date table and closed date.
My issue lies in getting the slicer to properly affect changes in the visualizations. I am using 'between' for the slicer. When adjusting the max date, the visualizations respond correctly. However, if I adjust the min date, no data is returned. What am I missing?
Thanks for any help!
Hi paz71,
Thanks for connecting with the Microsoft Fabric Community Forum and Thank you rajendraongole1 for your input.
Your approach using USERELATIONSHIP() is valid for handling inactive relationships. However, if adjusting the Min Date in the slicer is still causing issues, an alternative approach is to use a disconnected Date Table with TREATAS().
USERELATIONSHIP() works well when activating inactive relationships, but slicers may not always behave as expected. Using TREATAS() with a disconnected Date Table allows more control over filtering, ensuring that both Created Date and Closed Date respond correctly.
This method has been successfully used in similar scenarios, and it can help resolve the issue if USERELATIONSHIP() is not working as expected.
If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!
Thank you.
9 Replies
Hi paz71 - Since you have an inactive relationship between the Date table and the Closed Date, can you use the USERELATIONSHIP() approach still causes issues, consider creating a disconnected date table and using measures with TREATAS().
USERELATIONSHIP function (DAX) - DAX | Microsoft Learn
Using USERELATIONSHIP in DAX - SQLBI
Power BI DAX Tutorial: The USERELATIONSHIP Function (2022 - February)
Hope this helps.
if you can share data, that helps us from our end to check the same if still issue exist.
Hi paz71,
Thanks for connecting with the Microsoft Fabric Community Forum and Thank you rajendraongole1 for your input.
Your approach using USERELATIONSHIP() is valid for handling inactive relationships. However, if adjusting the Min Date in the slicer is still causing issues, an alternative approach is to use a disconnected Date Table with TREATAS().
USERELATIONSHIP() works well when activating inactive relationships, but slicers may not always behave as expected. Using TREATAS() with a disconnected Date Table allows more control over filtering, ensuring that both Created Date and Closed Date respond correctly.
This method has been successfully used in similar scenarios, and it can help resolve the issue if USERELATIONSHIP() is not working as expected.
If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!
Thank you.Hi paz71,
I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.
If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.
Thank you.
Hi paz71,
I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.
If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.
Thank you.
Apologies for the delayed response, but TREATAS() is not treating me well either. I created 2 measures (work recieved and work complete) with no success. I added the measures to the visuals as values, but once the measaures were added, output was null without even applying the slicer.
Work_Rcvd =
CALCULATE(
COUNTROWS(work_order),
TREATAS(VALUES(DateTable[Date]),
work_order[created])
)
Hi paz71
Thankyou for your follow-up!
It looks like you're on the right path using TREATAS(), but the blank output likely indicates that the measure isn't receiving the expected filter context.
Here are a few things to check:
Make sure the disconnected Date Table used in the TREATAS() measure is the same one connected to your slicer. If they’re not aligned, the measure won’t return any results.
If the matrix or table doesn’t include relevant fields (like category or date), the measure may return blank due to missing context. Try adding a basic column from the work order table to validate this.
Even before applying a slicer, the disconnected Date Table needs to have data in the current context. If the slicer or visual doesn’t trigger that, the measure will show null.
Happy to help! If this addressed your concern, marking it as "Accepted Solution" and giving us "kudos" would be valuable for others in the community.
Thank you.
paz71 Hey,
I will suggest you to create a category column based on closed date.Status =SWITCH(TRUE(),(financials[Date]) <>BLANK(), "Closed",ISBLANK(financials[Date]) , "In Progress")
you can filter the data as per your requirement.
Thanks
Harish M
Did I answer your question? Mark my post as a solution! Appreciate your Kudos
