Display selected values of hierarchy slicer on a card

[Anonymous]

Hi all,

I have a 2-level hierarchy slicer. 

I want to display the selected values. In order to achieve this, I use a measure that I found here on another thread :

Selected Slicer = 
SWITCH (
    TRUE (),
    ISFILTERED ( 'Table'[Level 2 ] ), CONCATENATEX ( VALUES ( 'Table'[Level 2 ] ), [Level 2 ], "," ),
    ISFILTERED ( 'Table'[Level 1] ), CONCATENATEX ( VALUES ( 'Table'[Level 1] ), [Level 1], "," )
)

This works great, instead of one case.

My issue is that when I choose a combination of a complete high level AND some lower level values, I get a display of all selected lower values. 

To demonstrate it in a clear manner:

This is the desired result

This is the desired result

This is NOT the desired result. Instead, I want the card to display "1,e".

Here are some sample data:

Level 1	Level 2
1	a
1	b
1	c
2	d
2	e
3	f
3	g

I hope this makes sense, it is my first attempt to ask for help so if I need to provide anything more pls let me know!

Thank you,

Kristina

[smodsy]

Hi there,

Thank you for this awesome solution... I've managed to adapt it to the three levels I have within my hierachy but for some reason it only ever displays the office level (level 3) for me?  Have I adapted it incorrectly?  

Location Slicer = 
VAR s = SELECTCOLUMNS ( 'Data', "Lev1", 'Data'[User Office Region], "Lev2", 'Data'[User Country], "Lev3", 'Data'[User Office] )
VAR b = ADDCOLUMNS (s,"res",
            IF ( COUNTROWS (
                    EXCEPT (
                        FILTER ( ALL ( 'Data' ), 'Data'[User Office Region] = [Lev1] ),
                        FILTER ( ALLSELECTED ( 'Data' ), 'Data'[User Office Region] = [Lev1] )
                    )
                ) = 0,[Lev1],IF ( COUNTROWS (
                    EXCEPT (
                        FILTER ( ALL ( 'Data' ), 'Data'[User Country] = [Lev2] ),
                        FILTER ( ALLSELECTED ( 'Data' ), 'Data'[User Country] = [Lev2] )
)
                                    ) = 0,[Lev2] ,[Lev3]))
    )
RETURN CONCATENATEX ( SUMMARIZE ( b, [res] ), [res], ", " )

Also, is there a way to adapt it so that if 'Select All' is use it displays 'All Locations' instead of the 40+ locations I have?

[lbendlin]

Still working on an answer, just wanted to say that you cannot use SWITCH in this scenario.  You need to calculate the result for each row of your data, and use the Level 1 value whenever the number of Level 2 filters is the same as the number of Level 2 values.

But that is rather tough to actually implement.  Nice challenge!

[lbendlin · Accepted solution]

Ok, this version seems to work for me. Please validate:

Selected Slicer = 
var s = SELECTCOLUMNS('Table',"Lev1",'Table'[Level 1],"Lev2",'Table'[Level 2])
var a = ADDCOLUMNS(s,"l2a",CONCATENATEX(filter(ALL('Table'),'Table'[Level 1]=[Lev1]),'Table'[Level 2],",")
                    ,"l2s",CONCATENATEX(filter(ALLSELECTED('Table'),'Table'[Level 1]=[Lev1]),'Table'[Level 2],","))
var b = ADDCOLUMNS(a,"res",if([l2a]=[l2s],[Lev1],[Lev2]))
var c = SUMMARIZE(b,[res])
return CONCATENATEX(c,[res],",")

Here's another version, slightly more concise, but same idea.

ss2 :=
VAR s = SELECTCOLUMNS ( 'Table', "Lev1", 'Table'[Level 1], "Lev2", 'Table'[Level 2] )
VAR b = ADDCOLUMNS (s,"res",
            IF ( COUNTROWS (
                    EXCEPT (
                        FILTER ( ALL ( 'Table' ), 'Table'[Level 1] = [Lev1] ),
                        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Level 1] = [Lev1] )
                    )
                ) = 0,[Lev1],[Lev2] )
    )
RETURN CONCATENATEX ( SUMMARIZE ( b, [res] ), [res], "," )

[avachris]

Hi there - Thanks this was a real useful piece of Dax!

[risa]

 Is there a way this same can be done for 4 level hierarchy slicer? and while using the above solution for 2 level slicer, I am getting some values when I dont select anything in the slicer...

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

[risa]

Thanks for the quick reply. Here is the sample table:

Index	L1	L2	L3	L4
1	A	A1	A12	A121
2	B	B1	B12	B121
3	C	C1	C12	C121
4	A	A2	A21	A212
5	B	B2	B22	B221
6	C	C2	C21	C211
7	A	A1	A11	A112
8	B	B1	B12	B121
9	C	C1	C11	C111
10	A	A2	A21	A211
11	B	B2	B22	B221
12	C	C2	C21	C212
13	A	A1	A12	A122
14	B	B1	B11	B111
15	C	C1	C12	C122
16	A	A2	A21	A211
17	B	B2	B22	B222
18	C	C2	C22	C221

The measures I used:

items = SWITCH (TRUE (),

    ISFILTERED ( Sheet1[L4] ), CONCATENATEX(ALLSELECTED( Sheet1[L4]), Sheet1[L4], " & ") ,

    ISFILTERED (  Sheet1[L2]), CONCATENATEX(ALLSELECTED(Sheet1[L2]), Sheet1[L2], " & ") ,

    ISFILTERED ( Sheet1[L3] ), CONCATENATEX(ALLSELECTED(Sheet1[L3]),Sheet1[L3], " & ") ,

    ISFILTERED ( Sheet1[L1]), CONCATENATEX(ALLSELECTED(Sheet1[L1]), Sheet1[L1], " & ")

)

Expected out come:

Expected outcome:

Below was not the expected outcome

Expexted outcome: A & B111.

How do I get the expected outcome here?

[lbendlin]

Same formula.

Measure = 
VAR s = SELECTCOLUMNS ( 'Sheet1', "Lev1", 'Sheet1'[L1], "Lev2", 'Sheet1'[L2], "Lev3", 'Sheet1'[L3], "Lev4", 'Sheet1'[L4] )
VAR b = ADDCOLUMNS (s,"res",
            IF ( COUNTROWS (
                    EXCEPT (
                        FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[L1] = [Lev1] ),
                        FILTER ( ALLSELECTED ( 'Sheet1' ), 'Sheet1'[L1] = [Lev1] )
                    )
                ) = 0,[Lev1],IF ( COUNTROWS (
                    EXCEPT (
                        FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[L2] = [Lev2] ),
                        FILTER ( ALLSELECTED ( 'Sheet1' ), 'Sheet1'[L2] = [Lev2] )
                    )
                ) = 0,[Lev2],IF ( COUNTROWS (
                    EXCEPT (
                        FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[L3] = [Lev3] ),
                        FILTER ( ALLSELECTED ( 'Sheet1' ), 'Sheet1'[L3] = [Lev3] )
                    )
                ) = 0,[Lev3] ,[Lev4])))
    )
RETURN CONCATENATEX ( SUMMARIZE ( b, [res] ), [res], " & " )

Your problem is that B11 only has one child. Therefore B111 is not honored.  Adding another value gives the desired result.

[risa]

I have 2 tables like the above, and are connected. Can I use 2 slicers(from both tables) and get the above desired output on a single card?

[lbendlin]

i cannot help you if you cannot provide appropriate sample data.

[risa]

Hi Ibendin, Thank you for the continued replies. I have included the pbix file link below. I used your measure here and having some bugs here. can you fix the measure for the card here to only display all the values selected in the the slicer1 and slicer2.

pbix:
https://drive.google.com/file/d/11oU0u6MmHFpmZiYbntMkYMQfRze4Stpn/view?usp=drivesdk

[lbendlin]

Your bugs are in your data and data model. Your formula works fine.

[Anonymous]

Is there a way to implement this with a hierarchy with 10 levels?

[lbendlin]

You can use the same methodology but I would seriously challenge the usefulness for your users.

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

[Anonymous]

Thank you for the quick reply. I actually don't need every node selected to display, I would just need the highest level in the hierarchy selected. For example if node e. Division level and f. Manger level are selected I would just need e. Division level to display. Does that make sense?

[lbendlin]

You can use FILTERS() for the levels you are interested in, but that will not give you the hierarchy.

[ststoian]

Thanks for this sollution, for me it works almowst perfectly except the fact that I have 8 levels into my hierarchy. How can I do this but with multiple nodes?
Thanks a lot 😉

[Anonymous]

Ibendlin this is great, works perfectly! I knew that switch would not do the trick and had tried so many things that didn't work. I had lost hope 😅. Thank you so much!

[lbendlin]

It was really tough for me as well. As the Italians say : " DAX is simple, it is not easy."  I am pretty sure there are more accurate and more performant solutions.  But for this scope (single slicer) it might be ok. 

What helps me is to break the problem down into baby steps and to use CONCATENATEX along the way to verify that I get the expected intermediate results. And I also learned that you should design a measure to work for the Totals, rather than for the individual "rows".