Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jas_power
Helper I
Helper I

Multi-select Non-additive Hierarchy Slicer - Identify slicer selections across hierarchy levels

Hello experts,

     I am using the custom hierarchy slicer to display a list of products with 7 levels in the hierarchy. The data itself is non-additive so I do not want to perform any type of aggregation because I have a value for every level in the hierarchy. This means, when a user selects 'Clothing and footwear' the table visual shows only 'Clothing and footwear' and grabs the value accordingly.

 

Slicer:

jas_power_0-1667918390699.png

 

Table Visual:

jas_power_1-1667918636008.png

 

This is achieved with two measures: 

 

Measure 1:

Determine which level is selected and return the associated product id. This works flawlessly when a user selects just one option in the slicer.

 

selectedProdId = 
SWITCH (
    TRUE (), 
    ISFILTERED ( 'prod_map'[level7] ), SELECTEDVALUE ( 'prod_map'[level7Id] ),
    ISFILTERED ( 'prod_map'[level6] ), SELECTEDVALUE ( 'prod_map'[level6Id] ),
    ISFILTERED ( 'prod_map'[level5] ), SELECTEDVALUE ( 'prod_map'[level5Id] ),
    ISFILTERED ( 'prod_map'[level4] ), SELECTEDVALUE ( 'prod_map'[level4Id] ),
    ISFILTERED ( 'prod_map'[level3] ), SELECTEDVALUE ( 'prod_map'[level3Id] ),
    ISFILTERED ( 'prod_map'[level2] ), SELECTEDVALUE ( 'prod_map'[level2Id] )
)

 

 

Measure 2:

Create a flag to identify if the product id from measure 1 matches the product id in the fact table ('W_data').

 

productFilter = if([selectedProdId] = min('W_data'[prodId]),1,0)

 

 

 To note, without measure 2, my table visual displays all the data under the level selected which is not the desired result.

 

Now that you're all caught up, my use case has changed and I now need to allow the user to select multiple items from the product slicer. Here's where I'm stuck: When the user selects products from different levels, the results are returned for all products at the lowest level selected.

 

Slicer selection across levels:

jas_power_2-1667919595842.png

 

Table visual result:

jas_power_3-1667919688270.png

Since 'Children's Clothing' is level 4, all the level 4 products under 'Alcoholic beverages, tobacco products...' is populated in the table visual. The behaviour makes sense, but I need to figure out how to change the behaviour so that the result somehow assesses if all the children are selected and keeps moving up the levels so the result is only 'Alcoholic beverages, tobacco products...' and 'Children's clothing'. 

 

I want the user to have the option to select as many products as they want. I need them to be able to deselect specific products if desired. This needs to be dynamic and a combination of any levels should be allowed. 

 

Here is a scenario and the expected result.

Scenario: User selects two level 3 products ('Alcoholic beverages', 'Clothing') and then deselects 1 product ('Men's clothing' - level 4) under 'Clothing' (level 3). 

jas_power_4-1667920764604.png

Current result but not desired:

jas_power_5-1667920846803.png

Result: The table visual should show 'Alcoholic beverages' since the user did not deselect anything it is the highest product selected in that group. Since the user deselected 'Men's clothing', only the remaining level 4 siblings in that group should show in the table visual ('Children's clothing, 'Women's clothing'). 

 

I am happy to supply additional information if requested. I would also appreciate your opinions on if it's possible or not, or even suggestions on alternative ways to allow the user this functionality. I can get narrowly focused sometimes and not think outside the box so maybe there's something out there that I'm not considering. Thanks in advance to anyone who takes the time to comment. 🙂 

 

 

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

Hi @jas_power ,

 

What you want is not to display any value below level three when the slicer doesn't have all selected at level four, right?

If so, you could create a measure as a visual-level filter for this.

Here, I will show through the four levels of slicers of year, jealousy, month and day as an example.

What you want is not to show the values in the red box.

3.png

Create a measure to return 1 when the number of days below level three(month) are chosen.

 

Measure = var _exclude=CALCULATE(COUNT('Table'[Day]),FILTER(ALL('Table'),[Month]=MAX('Table'[Month])))
var _incluede=CALCULATE(COUNT('Table'[Day]),FILTER(ALLSELECTED('Table'),[Month]=MAX('Table'[Month])))
return IF(_exclude=_incluede,1)

 

Put it into the visual-level filter, set up show items when the value is 1. The formula principle is to calculate the count of level four without the interference of slicer selection and the count of level four under slicer selection respectively, and then compare the two values, equal means that the values of level four below level three are selected.

4.png

 

 

Best Regards,

Stephen Tao

 

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

@v-stephen-msft you are so kind to respond. I appreciate the attempt. Unfortunatly, I think there was some miscommunication. To clarify, using your example, since all days are selected under January, I do not want to see the days under January because January already includes 1, 2. In the other scenario, since only 3 is selected, I only want to see the day 3 because April includes day 4 in its value already so I don't want to see April or 4. I hope that helps clarify a bit. 

Hi @jas_power ,

 

Sounds like field parameters might help you.

Please refer to

Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Learn

 

 

Best Regards,

Stephen Tao

 

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

Hi @v-stephen-msft - this could be an interesting approach. Thank you for your suggestion. It will take me some time to set my data up to try, but I wasn't even aware this was an option so thank you for that! I will give it a go next week.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.