Forum Discussion
Slicer issue.
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.
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,
Suman
Hi 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