Forum Discussion
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 date table. I have also created a one-to-many relationship between these tables.
Currently, when I select a month in the Month slicer, the Department slicer does not dynamically filter the departments for that selected month. For example:
If January is selected, the Department slicer should display only the departments related to January.
If February is selected, the Department slicer should display only the departments related to February.
Could you please advise how this can be achieved?
Thanks and regards,
Suman
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.
13 Replies
Based on the description, it appears that your Department slicer is coming from a table that isn't directly filtered by the Month slicer. Relationships in Power BI do not automatically propagate from one table to the other even if the dimension tables directly filter the same fact table. You can create a measure that counts the rows in the fact table and use it as a visual filter for the Department slicer. Example: COUNTROWS (datatable) - use this as not blank visual filter.
Hi Suman2505855,
Altough I don't know how the model based on your description if your data has information for the department on the fact table the slicer should be influenced in the correct way showing only the departments that are on that specific dates:
If the dimension table for the department is a separate table it won't do the filtering.
Can you please confirm if the department is on the fact table or is it a separate dimension table.
Hi MFelix,
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.
Thanks and regards,
SumanHi Suman2505855 ,
Has I refered if you are getting the information from the Fact table for the description this should work properly, since the relationship is one-to-many from the calendar to the fact so the Departments should be filtered by month.
Don't know the amount of values you have in the Departments but I assume that is not million of unique records.
Concerning the experience you are doing it's impacting the other results because of the direction of the relationships that is being used between your depmonthbrige and sheet1.
I believe that the better approach is the one refered by danextian so you should try the following:
- Create a dimension table for departments
- Create a one-to-many relationship with the fact table
- Add new measure to you model similar to the one below
Filter Fact Table = COUNTROWS(FACT_Table)- Use the new dimension department for the slicer
- Add the Filter Fact Table to the filter of the slicer and select is not blank
This will filter out the departments that are active see images below:
Has you can see in the images the values on the slicer match the values on the table (there is no interaction between the slicers and the table for the purpose of showing the information.
This approach allows you to apply the filter only on the department visual keeping everything correct in calculations.
Be aware of one problem with this approach that someone selects a department on your case that from one month to the other it's not visible then the slicer will show that value until someone changes the selection check image below with the Yellow colour
On the second image the value appears on the slicer because it's already selected before I change the year
When I change the selection the yellow dissapears
Hi Suman2505855 ,
This behavior isn’t really determined by whether the department is in a fact table or a dimension table.
In Power BI, slicers don’t automatically filter each other, even if there are relationships. The Month slicer will filter the data, but the Department slicer continues to display all values unless we set it up otherwise.
One straightforward approach is to create a measure that checks if a department has data for the selected month, and then use this measure to filter the slicer. This ensures only relevant departments appear dynamically.
I hope this clarifies the situation. If I’ve misunderstood any part of your situation, please let us know.
Hi V-yubandi-msft ,
Apologies for the correction but if the values of the tables you use in the slicer are part of related tables they will filter out each other, this however depends on the filter type single or bidirectional. If you have one slicer from the Dimension table and another one with a column from a fact table the first slicer will filter the second one, however has refered by different users on this post this does not happen if you are refering to two dimension tables and in that case you are correct about the use of the measure.
Thanks for the clarification. That makes sense, especially the point about how it behaves differently depending on whether the slicers are coming from fact or dimension tables. Appreciate the detailed explanation.
Hi V-yubandi-msft,
Could you please provide the DAX functions required to achieve this?
Thanks and regards,
SumanHi 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.
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 -> Dim22 - 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.
ThanksIf 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-manivasaganCheers!
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.
Hi Suman2505855 ,
May I know if your issue has been resolved or if you’re still facing any problems? I’ve shared the response along with the PBIX file. Please take a moment to review and let me know if any changes are needed.
Thanks.
Hi Suman2505855 ,
Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.
