Apply a filter to a slicer based on a filter in a unrelated table

[ehew1991]

I would like the "options" in my slicers to populate based on a filter applied to a unrelated table (I cant create a active relationship - will explain),  is this possible?

Basically my dashboard is a staffing dashboard looking at multiple staffing aspects for example Sickness/Turnover/Vacancies within a business.  Each of the staffing metrics are held in different tables and collected slightly differently, they also may have slightly different date periods and possible naming conventions for teams etc - so they cannot be all added to the same table. 

Within the staffing metric tables, along with the data and the organisation code, there are 3 columns that are consistent with each other and will have the same naming conventions. So i have made a master list of these (removing duplicates) and added these as slicers on each tab. These are "Business site", "Staff Group", and "Business service area". Then i have 2 slicers for "team name" - sickness and turnover match, and then vacancy is a seperate slicer. So that the user can filter the staffing data from the chosen business location based on what level they want to view the data (by team, or by staff group etc). 

Now if this was just for 1 business location, the dashboard would work absolutely fine. However, the staffing metric tables hold data for multiple business locations (note this is different to business sites) - these locations are then broken down as mentioned before (Business site, staff group, and business service area). The business location information is held in a seperate table called "Current BLs". This table holds information such as location name, organisation code, local authority, address, county, managing team etc. At the beginning of my dashboard (first tab) the user can filter either by singular location, County, Area of england, and managing team - or they can choose not to filter and view the staffing data as a whole business level including all locations across england.  To connect this "Current BLs" table to my staffing metrics, i have created a relationship between the organisation code columns in the staffing tables (Turnover, Vacancy, Sickness) and the Current BLs.

So this all works perfectly fine, if the user does not select a filter on the first tab, the data shows for all locations in England, if they select a area it shows all data correctly, and same goes if they choose a singluar location. The data is all pulling correctly.

However, my issue is that the 3 slicers I mentioned initially that connect all staffing tables together (so actual headcount from turnover can be shown alongside planned headcount from vacancy on the same visual based on the slicers applied) are not connected to the "Current BLs" table. Therefore, if a single location is selected on the 1st tab, the slicers still show all of the 100s of different "Business sites", "Staff Groups", and "Business service areas" for all of the locations in England, rather than just the 1 selected location. Therefore, its impossible for the user to sift through the 100s of sites to find the site in the chosen location they want to look at. 

I just want the slicers to populate with the handful of sites/staff groups/service areas at the chosen location (or area etc), not to show every single site in England.

I can't create a active relationship between Current BLs table and the 3 "master list" tables for the slicers as I get the "ambiguity error".

I tried playing around with TREATAS, but that just brings back the organisation code selected in "Current BLs". I cant then get it to do anything to the slicer options.

This is the code i experimented with to bring back the Org code:

FilterBLsSelected = CALCULATE(SUMMARIZECOLUMNS('Business service area list'[org code], TREATAS ( VALUES ('Current BLs'[Org ID]), 'Business service area list'[org code]) ))

I have also tried merging the tables so that the "Business sites", "Staff Groups", and "Business service areas" were added columns on the Current BLs table (merged by org code), but then the data could not be broken down properly in my visuals to the level i wanted as i couldnt create a relationship between each of the 3 columns and each staffing table.

Does any one have any idea how i will get the slicers to display only the options required based on the filter applied to "Current BLs" on the first tab.  I feel like it should be such a simple fix, but I have googled for ages to find a solution and nothing has came up yet.  thank you in advance. 

[vivran22]

Hello @ehew1991 

Can you share sample data/pbix file as well? Your problem is quite descriptive, however it would save time and effort with the sample tables.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[ehew1991]

This is the current relationship pane and how it looks.

My tables are pretty much set up as follows:
Current BLS

Org Code	Location name	Location address	Local Authority	Managing Team	Post Code
123	Blue	(uk address)	London	South East	(uk postcode)
124	Red		Yorkshire	Northern

&so on.....

Turnover / Sickness/ Vacancy all look similar so here is an example of one:
Turnover

Org code	Site	Staff group	Service area	Team name	Month ID	Leavers	Headcount
123	Blueish	Office workers	Distibution	Team1	Jan	2	20
123	Bluey	Factory	Distribution	Team2	Jan	1	25

& so on...  all info for location "123", then "124" will be underneath, then the next location etc.

Then i have my master lists that i use for slicers so i can filter each of Turnover/Sickness/Vacancy by "Site", "Staff Group", and "Service area"
For example for my Sites Master table, I just have a list of all sites, and then the org code.

Site Name	Org Code
Blueish	123
Bluey	123
Redish	124
Redy	124

The easiest way to make it work is to create a relationship between those Master slicer tables and the Current BLs,  but since Current BLs has a relationship to each of the tables (Turnover, Sickness, Vacancy), as do the Master lists, i cannot create a relationship between Current BLs and the Master Tables (Sites, Service Areas, Staff groups).

I just want the slicers to show the information only for the organisation selected on the cover page. So for example, if the user selected location RED on the cover page, then the site slicers should only show "redish" and "redy".  but at present, if red is selected it shows all options including bluey & blueish.
The data in the visuals is all correct due to the relationship between those and Current BLs, and if i sift through the slicer options it does apply the correct filter.   I just dont want all the options in the slicers. seems such a trivial issue but it would make the dashboard much more user friendly. 

Thank you for your help 🙂

[v-xicai]

Hi @ehew1991 ,

For the relationship, recommend you create the star schema instead of circle schema, so you may delete the secondary relationships among these tables, and change the Cross filter direction of relationships among the these tables above from Single to Both , which will take these tables treated as a single table.  

In your scenario, you may delete one secondary relationship in order to create relationship between those Master slicer tables and the Current BLs . Then when you make some changes or interact in someone table, the other table objects will return corresponding matched result.  See more:Create and manage relationships in Power BI Desktop .

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ehew1991]

Hi thank you for reply.

When I try to change the single filter direction to "both", i get the following error message:

"The relationship you're creating lets you filter Sickness by Turnover, but power bi allows only one filtering path between tables in a data model. Deactivate existing relationships between the tables or change their filter direction. This relationship can be added as one-way filtering"