Creating Hierarchy Slicers with Overlapping Groups

[Anonymous]

Dear Community, 

I am stuck with the issue to create a nested / hierarchy slicer with one level being a overlapping group. 

Specicifically I am creating a staff qualifications dashboard with the data table that includes the following columns:  staff names, tech qualfication item (such as Power BI, Excel, Power Point, SAS, etc), experience level (0, 1-2 yrs, 3-5 yrs, 6-9 yrs, 10 years or more). 

The dashbaord page would contain a slicer/s that can filter out the list of staff with the specific experience levels of selected qualification items. Right now I am using a hierarchy slicer: qualification item & experience level, which can be used to filter the data to get the list of staff names in a table.

However, the managers would like to use a different experience level calculation. Instead of the current exclusive categories, they would like to use the overlapping categories: 3 or more years, 6 or more years, 10 years or more. I told them they can achieve this by selecting mutilple experience levels, such as selecting 6-9 yrs & 10 years or more for "6 or more years". But they still think it preferrable to use the overlapping categories in the hierarchy filter: qualification level + overlapping group (3 categories).it 

Originally i thought it would be pretty easy with a parameter slicer that consist of the 3 seperate columns I created for the 3 categories. But it seems the parameter slicer won't work in a hiearchy nested slicer. In preivous posts on overlapping groups as slicer, people have discussed the possibility of creating a measure, a separate table witht the categories, a switch, etc. But I don't know how these will apply to my case, and how to create the Dax measure. 

Your help and advice is much appreciated.    

[amitchandak]

@Anonymous , Create measures for those and then create a calculation group. That will convert set of measures into dimension

Power BI Model Explorer with calculation group authoring
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-model-explorer-with-calculation-group-authoring-and-creating-relationships-in-the-properties-pane/

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

[Anonymous]

Hi @amitchandak Thanks for your responses. 

Based on the instructions in your vidoes, I did create a measure based on a disconnected table of list of overlapping Years group categories. First, I created a Years Overlapping field in the data table based on the original Years (exclusive group) field. Then I created the measure using the search words function to search the year group included in the disconnected table of year groups list. I can used the measure as a filter=1 and the Years list field as a slicer. See screenshot below.  

However, when I try to use the "Years List" field to replace the original (Exclusvie) Years field in the hierarchy slicer on the left, there is an error message: you are using fields that dont have a supported set of relationships. So I cannot use the measure in the hierarchy slicer. Althoug it can be used in a slicer by itself. That means, the Years slicer will apply to all selected categories, instead of different combinations. See Screenshot below:

Do you have any suggestions on this? 

Thanks.

Lijun