Table visual to display selected value from hierarchy slicer not the levels below.

[jas_power]

Good morning,

    I am using the Hierarchy Slicer to display a list of products and their sub-categories. This is essential as the products have multiple levels and it would be a poor user experience to list 300+ products in a regular slicer. Not to mention I require the ability to remove blank entries due to a ragged hierarchy.

    My problem lies in the table visual tied to the slicer. When a user selects a product from the Hierarchy Slicer, the table displays all levels below the product selected even though single select is enabled. My data is set up where I have a specific value tied to each product no matter where it lies in the hierarchy so I do not want to display any of the products below the level selected. The hierarchy is simply for display purposes only.  Ideally, the hierarchy slicer would only show one radio button selected and not all the buttons underneath it, but I can accept that if necessary as long as the table visual only shows the values for the product that is selected.

I have a measure that tells me which product is selected from the Hierarchy Slicer. When I use that in the table visual I get the correct product name but the table shows all the values associated with all the products underneath the selected product.

Is there any way around this behaviour?

[jas_power]

Here's how I would like the hierarchy slicer to behave if I select 'Eggs':

I achieved this by using a regular slicer with no hierarchy.

So, can the same behaviour be achieved using a hierarchy slicer or am I stuck giving the user a regular slicer with a giant list of products?

[jas_power]

I want to show how my table visual behaves without using @AlexisOlson suggestion. As you can see, the Geography and Date slicers are working as expected, where the results are only showing what was selected from the slicer. The Product slicer on the other hand is showing everything from the fact table if it shares the same ultimate parent. 

I need the table visual to only show the records associated with 'Eggs' in this example.

[jas_power]

I have the following measure which tells me which product is selected from the product slicer. 

Selected Product Incl = 
SWITCH (
    TRUE (),   
    ISFILTERED ( 'prod_metadata_incl'[level5Name] ), SELECTEDVALUE ( 'prod_metadata_incl'[level5Name] ), 
    ISFILTERED ( 'prod_metadata_incl'[level4Name] ), SELECTEDVALUE ( 'prod_metadata_incl'[level4Name] ),    
    ISFILTERED ( 'prod_metadata_incl'[level3Name] ), SELECTEDVALUE ( 'prod_metadata_incl'[level3Name] ),    
    ISFILTERED ( 'prod_metadata_incl'[level2Name] ), SELECTEDVALUE ( 'prod_metadata_incl'[level2Name] ),    
    ISFILTERED ( 'prod_metadata_incl'[level1Name] ), SELECTEDVALUE ( 'prod_metadata_incl'[level1Name] ),  
    "Select a product group to include."
)

When I use the measure in the table visual, all it does is replaces the Product name in the table visual but keeps all the extra rows for all of the other products that you see in the screenshot in the previous message. 

[v-yalanwu-msft]

Hi, @jas_power ;

I still have some doubts about your question. Do you not understand why the value after Dairy Product and eggs is more than 500 pairs? What was the result of your single Dairy Product selection? Or you can illustrate it with a simple statistic and show the result you want.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jas_power]

Hi @v-yalanwu-msft. I assume Dairy products and eggs has a higher value because it's aggregating since when you select Dairy Products and eggs, it selcts all the items that fall underneath it (Dairy Products, Eggs).

I just want to reproduce my fact table in a table visual filtered based on my slicers without any aggregations. I know it works great if I don't use a hierarchy slicer and just list the products in a normal slicer, but I wanted to aid the user in selecting a product by showing them in a hierarchy. 

When I select Dairy Products I get this:

So there is still some aggregating going on. The value gets higher as you go up the hierarchy. 

Here is my fact table filtered for the same reference period (189 = december 1 1997)  and geography (9 = New Brunswick) from my slicers. I filtered it for the three products in question: 

Here are those prodIds from my product dimension table:

As you can see, I only have one result for each product in that period and geography in my fact table. I am trying to display the indexValue.

What I want, is a simple replication of my fact table in a table visual (with dimension labels instead of id's) to start. I have a lot of calculations to do, but I can't start on that until I know I am getting the correct basic value from the fact table. 

Hope that explains things a little better. Perhaps I just need to rethink the hierarchy slicer altogether.

[jas_power]

@AlexisOlson  It's not always the lowest level. A user should be able to select any of the products and get a single result. The level could be 1, 2, 3 and should still get one result. Here's some screenshots and my file is so massive.

Here is how it should work for all levels (but is only working for the lowest). The first image is the slicer selection which is eggs:

 

The second image is the table visual result which is as expected. There is one row for each refPeriod (date) in the range selected.

If I go up one level in the slicer to Dairy products and eggs:

The result is a value that is way too high. The value is always under 100 for one product in one reference period in one geography:

It's hard for me to pinpoint exactly what the value is made up of. It seems to be more than just all the levels added together for a date and geo.

I will continue to try to shrink my power bi more so that I can share it.

Hope this helps.

[AlexisOlson]

This is helpful. I think I finally understand your question and I'll try to get back to you later.

[jas_power]

My apologies for not outlining it clearer. Thank you for looking into it. I forgot to answer your question: if a user selects a geography and date but not a product, I was going to try to implement something where the user has to select a value from each slicer or the table won't load. 

[AlexisOlson]

So this isn't super elegant and can likely be refined significantly but I think I managed to get something working more or less like I think you're after. Plenty of room for tweaking though.

This should return blanks below the highest level you've selected:

SumValue_HideLowerLevels =
VAR L3 = SELECTEDVALUE ( Products[Level3] )
VAR L2 = SELECTEDVALUE ( Products[Level2] )
VAR L1 = SELECTEDVALUE ( Products[Product] )
VAR L3_Result = [SumValue]
VAR L2_Result = CALCULATE ( [SumValue], ALL ( Products[Level3] ) )
VAR L1_Result = CALCULATE ( [SumValue], ALL ( Products[Level2], Products[Level3] ) )
VAR L3_Blank = ISBLANK ( L3 ) || ( L3 = "" )
        || ( L3_Result = L2_Result )
        || ( COUNTROWS ( ALLSELECTED ( Products[Level3] ) ) > 1 )
VAR L2_Blank = ISBLANK ( L2 ) || ( L2 = "" )
        || ( L2_Result = L1_Result )
        || ( COUNTROWS ( ALLSELECTED ( Products[Level2] ) ) > 1 )
VAR L1_Blank = ISBLANK ( L1 ) || ( L1 = "" )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Products[Level3] ) && L3_Blank, BLANK (),
        ISINSCOPE ( Products[Level2] ) && L2_Blank, BLANK (),
        ISINSCOPE ( Products[Product] ) && L1_Blank, BLANK (),
        L3_Result
    )

See attached. Sample screenshot:

[jas_power]

@AlexisOlson  Thank you so much for taking the time to come up with a solution! I very much appreciate it. It's an interesting approach. I am still learning Power BI so am still trying to wrap my head around what it's doing. This works great when the lowest level is selected, but I am having trouble tweaking it to not sum at the higher levels if a higher level is selected from the slicer. My data table has one value for each product (when taking into account the date and geography dimensions). I will never need it to sum the same product as a product will never repeat across date and geography. 

Since it works great at the lowest level, I tried repeating the lowest level syntax for the level_result variables for all the levels, but without success. 

I think it would help to explain my model a bit.

I have a data table that contains the values and dimension tables for product, date and geography. The product dimension table is a flattened hierarchy so I can create the hierarchy slicer similar to what you have in the attached power bi workbook. What is happening when I apply the measure to my data is that the lowest level produces a single table value which is exactly what I'm looking for, but when I select a higher level, it sums everything underneath it. 

Here is some sample data more akin to what my fact table looks like:

Date	Geo	Product	Value
2022-01-01	Canada	A	2
2022-01-01	Canada	x	6
2022-01-01	Canada	y	3
2022-01-01	Canada	m	4
2022-01-01	Canada	n	7
2022-01-01	Canada	B	1
2022-01-01	Canada	w	2
2022-01-01	Ontario	A	1
2022-01-01	Ontario	x	3
2022-01-01	Ontario	y	4
2022-01-01	Ontario	m	6
2022-01-01	Ontario	n	2
2022-01-01	Ontario	B	1
2022-01-01	Ontario	w	3
2022-02-01	Canada	A	4
2022-02-01	Canada	x	8
2022-02-01	Canada	y	3
2022-02-01	Canada	m	3
2022-02-01	Canada	n	4
2022-02-01	Canada	B	2
2022-02-01	Canada	w	1
2022-02-01	Ontario	A	2
2022-02-01	Ontario	x	1
2022-02-01	Ontario	y	3
2022-02-01	Ontario	m	8
2022-02-01	Ontario	n	3
2022-02-01	Ontario	B	4
2022-02-01	Ontario	w	3

My desired output is pretty much a replica of my fact table filtered based on the slicer selections for the dimensions so there is only one result per product, date and geography. It's like I need the hierarchy for the purposes of the slicer but I don't want that hierarchy to carry into the table visualization.

Please let me know if this is not clear. Thanks!

[AlexisOlson]

Can you show me what you expect to see at each level with a couple of examples? I'm not following how you expect it to behave there.

[jas_power]

@AlexisOlson For sure!

At its most basic I want the table visual to show just the single value from the fact table.

If user selects '2022-01-01' from the date slicer, 'Canada'  from the geography slicer and 'A' from the product slicer, the table visual will display 2 as the value. If user selects '2022-02-01' from the date slicer, 'Ontario' from the geography slicer and 'm' from the product slicer the table visual will display 8.

Let me know if that helps. I'll start working on reducing my power bi file so I can attach it here in case it's needed.

Thanks again!

[AlexisOlson]

Right. That's selecting at the lowest level though. You don't need any DAX to make that work.

What if the user selects '2022-01-01' > 'Canada' but doesn't make any product selection?