Forum Discussion

Suman2505855's avatar
Suman2505855
Regular Visitor
5 months ago
Solved

Slicer issue.

Hi Team, I need your support regarding a slicer issue in my Power BI report. I have created a Department slicer using data from the HR data table and a Month slicer using data from the Calendar dat...
Need Help
  • V-yubandi-msft's avatar
    V-yubandi-msft
    5 months ago

    Hi Suman2505855 ,

    You can achieve this by creating a simple measure and using it as a filter on the Department slicer.

    Dept Visible =
IF(
    CALCULATE(COUNTROWS('HR Table')) > 0,
    1,
    0
)

    After creating this measure, select the Department slicer and add this measure to the Filters pane. Set the filter to is 1.

    This will ensure the slicer shows only departments that have data for the selected month.

    FYI:

     

    For more details, I’ve attached the PBIX file. Please take a look and let me know if anything needs to be adjusted or if you need any additional information.

     

     

Natarajan_M's avatar
Natarajan_M
Super User
5 months ago

Hi Suman2505855 , 

I just created an illustration of how the relationship works in pbi .

Single direction relationship: filtering behaviour will pass from the dim1 -> fact  ->X dim2



1 - The problem. Your Month slicer filters the Calendar table -> filter flows into HR Data (fact) via the 1:M relationship. But the filter stops there. It never crosses back up into the Department dimension because the relationship is single-direction (Calendar -> Fact -> stops). The Department slicer sees no signal.

 

-----------------------


Bi direction : filtering behaviour will pass from the Dim1 ->  fact -> Dim2 

 

2 - Fix A: Bidirectional. Flip both relationships to "Both" in the relationship editor. Now filter flows all the way through: Calendar -> Fact -> Department, and the Department slicer dynamically shows only departments that have rows in the selected month. The caveat is that in complex models with many tables, bidirectional can create ambiguous filter paths and unexpected results.

Thanks

If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan

Cheers!

Suman2505855's avatar
Suman2505855
Regular Visitor
5 months ago

 

Hi Natarajan_M ,

Thank you for your investigation and support.

Below is the screenshot of my data model. The HR Data table is the fact table, and the Calendar table is used as the date dimension. For the Month slicer, I am using the column from the Calendar table, and for the Department slicer, I am using the Department Description column from the HR Data table (highlighted in yellow).

The expected behavior is that when I select February in the Month slicer, the Department slicer should display only the departments related to February. Similarly, when January is selected, it should show only the departments related to January.

However, based on the current model view, the Department slicer is displaying all departments and is not being filtered by the selected month.

In the screenshot, the section highlighted with a circle is something I tested internally, and it appears to work as expected. However, when applied to other visuals in the report, it is returning incorrect values.

Could you please help me review this and suggest how to resolve the issue?

Thank you for your support.