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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Elieekh17
Frequent Visitor

Display slicer hierarchy values

Hello Community,

 

I have a slicer with hierarchy: level 1: Group Header Code and Level 2 : Group Header Name. However when I select any value from the level 2 it displays automatically the relevant one in level 1 but If I choose the level 1 value from the column Group Header Code it only display the value without the second level.

 

SelectedValue  =
VAR Delimiter = " / "
VAR FilteredValues =
    FILTER (
        {
            IF (
                ISFILTERED ( SUPPLIERS[Group Header Code] ),
                "Group Header Code: "
                    & CONCATENATEX (
                        FILTERS ( SUPPLIERS[Group Header Code] ),
                        SUPPLIERS[Group Header Code],
                        ", "
                    ),
                BLANK ()
            ),
                IF (
                ISFILTERED ( SUPPLIERS[Group Header Name] ),
                "Group Header Name: "
                    & CONCATENATEX (
                        FILTERS ( SUPPLIERS[Group Header Name] ),
                        SUPPLIERS[Group Header Name],
                        ", "
                    ),
                BLANK ()
            )},
        NOT ( ISBLANK ( [Value] ) )
    )
RETURN
     "Suppliers : "
        & CONCATENATEX ( FilteredValues, [Value], Delimiter )

Can you please help

many thanks
1 ACCEPTED SOLUTION
v-jtian-msft
Community Support
Community Support

Hi,@Elieekh17 I am glad to help you.

According to your description, you have encountered this problem after creating a measure: when the slicer selects only the first level of the field, the slicer will automatically include all the values of the second field to be automatically checked, but in visual the total column of the measure only displays the value of the first level, but not the value of the second level which is automatically selected by the system.

But you want the total column to display both level1 and level2 values when the slicer selects level1.

like this.

vjtianmsft_0-1718682319463.pngvjtianmsft_1-1718682329701.png

If my understanding is correct, you can refer to my test below

The results of the test are as follows:

vjtianmsft_2-1718682348271.png

Here is  the DAX code

 

M_test = 
VAR Delimiter = " / "
VAR allLevel2_values=CALCULATE("Group Header Name: "
                    & CONCATENATEX (
                        FILTERS ( SUPPLIERS[Group Header Name] ),
                        SUPPLIERS[Group Header Name],
                        ", "
                    ),FILTER(ALLSELECTED('SUPPLIERS'),'SUPPLIERS'[Group Header Code]IN SELECTCOLUMNS('SUPPLIERS',"level2_values",'SUPPLIERS'[Group Header Code])))

VAR FilteredValues =
    FILTER (
        {
            IF (
                ISFILTERED ( SUPPLIERS[Group Header Code] ),
                "Group Header Code: "
                    & CONCATENATEX (
                        FILTERS ( SUPPLIERS[Group Header Code] ),
                        SUPPLIERS[Group Header Code],
                        ", "
                    ),
                BLANK ()
            ),
                IF (
                ISFILTERED ( SUPPLIERS[Group Header Name] ),
                "Group Header Name: "
                    & CONCATENATEX (
                        FILTERS ( SUPPLIERS[Group Header Name] ),
                        SUPPLIERS[Group Header Name],
                        ", "
                    ),
                BLANK ()
            )},
        NOT ( ISBLANK ( [Value] ) )
    )

RETURN
    IF(ISFILTERED(SUPPLIERS[Group Header Code])&&NOT(ISFILTERED(SUPPLIERS[Group Header Name])),
        "Suppliers : "
        & FilteredValues & Delimiter & allLevel2_values  ,
        "Suppliers : "
        & CONCATENATEX ( FilteredValues,  [Value], Delimiter )
    )

 

vjtianmsft_3-1718682392948.png

Here is my test data:

vjtianmsft_4-1718682412564.png

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

View solution in original post

2 REPLIES 2
v-jtian-msft
Community Support
Community Support

Hi,@Elieekh17 I am glad to help you.

According to your description, you have encountered this problem after creating a measure: when the slicer selects only the first level of the field, the slicer will automatically include all the values of the second field to be automatically checked, but in visual the total column of the measure only displays the value of the first level, but not the value of the second level which is automatically selected by the system.

But you want the total column to display both level1 and level2 values when the slicer selects level1.

like this.

vjtianmsft_0-1718682319463.pngvjtianmsft_1-1718682329701.png

If my understanding is correct, you can refer to my test below

The results of the test are as follows:

vjtianmsft_2-1718682348271.png

Here is  the DAX code

 

M_test = 
VAR Delimiter = " / "
VAR allLevel2_values=CALCULATE("Group Header Name: "
                    & CONCATENATEX (
                        FILTERS ( SUPPLIERS[Group Header Name] ),
                        SUPPLIERS[Group Header Name],
                        ", "
                    ),FILTER(ALLSELECTED('SUPPLIERS'),'SUPPLIERS'[Group Header Code]IN SELECTCOLUMNS('SUPPLIERS',"level2_values",'SUPPLIERS'[Group Header Code])))

VAR FilteredValues =
    FILTER (
        {
            IF (
                ISFILTERED ( SUPPLIERS[Group Header Code] ),
                "Group Header Code: "
                    & CONCATENATEX (
                        FILTERS ( SUPPLIERS[Group Header Code] ),
                        SUPPLIERS[Group Header Code],
                        ", "
                    ),
                BLANK ()
            ),
                IF (
                ISFILTERED ( SUPPLIERS[Group Header Name] ),
                "Group Header Name: "
                    & CONCATENATEX (
                        FILTERS ( SUPPLIERS[Group Header Name] ),
                        SUPPLIERS[Group Header Name],
                        ", "
                    ),
                BLANK ()
            )},
        NOT ( ISBLANK ( [Value] ) )
    )

RETURN
    IF(ISFILTERED(SUPPLIERS[Group Header Code])&&NOT(ISFILTERED(SUPPLIERS[Group Header Name])),
        "Suppliers : "
        & FilteredValues & Delimiter & allLevel2_values  ,
        "Suppliers : "
        & CONCATENATEX ( FilteredValues,  [Value], Delimiter )
    )

 

vjtianmsft_3-1718682392948.png

Here is my test data:

vjtianmsft_4-1718682412564.png

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

Thank you @v-jtian-msft  it worked and issue solved. Many Thanks

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.