cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Alrythmond Regular Visitor
Regular Visitor

Display lowest Hierarchy slicer field selected

Hi everyone,

 

I am new to power bi and DAX, I have a hierarch slicer and a card to display what I have selected in the slicer how do I do this in DAX?

 

here is a screenshot of what I want to achieve

ask2.PNG

once I select on new zealand it should display new zealand, when I select 2079 it should only display 2079, when I select an employee is should display only the employee for example 1006098 - Simon Wall

 

 

Thanks in advance for your responses

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Display lowest Hierarchy slicer field selected

@Alrythmond 

 

May be.. something like this.

Checking if the lowest level is filtered>>then next upper level>>then next

See the attached sample file as well

 

Measure =
SWITCH (
    TRUE (),
    ISFILTERED ( Table1[Level3] ), CONCATENATEX ( VALUES ( Table1[Level3] ), [Level3], "," ),
    ISFILTERED ( Table1[Level2] ), CONCATENATEX ( VALUES ( Table1[Level2] ), [Level2], "," ),
    ISFILTERED ( Table1[Level1] ), CONCATENATEX ( VALUES ( Table1[Level1] ), [Level1], "," )
)

View solution in original post

Super User
Super User

Re: Display lowest Hierarchy slicer field selected

@Alrythmond 

 

I see that order of levels 2 and 3 in your MEASURE was not correct.

 

Use this MEASURE

 

Selectedslicer =
SWITCH (
    TRUE (),
    ISFILTERED ( FACT_UTE[Employee] ), SELECTEDVALUE ( FACT_UTE[Employee] ),
    ISFILTERED ( FACT_UTE[SG_Code] ), SELECTEDVALUE ( FACT_UTE[SG_Code] ),
    ISFILTERED ( FACT_UTE[OC] ), SELECTEDVALUE ( FACT_UTE[OC] ),
    ISFILTERED ( FACT_UTE[Region] ), SELECTEDVALUE ( FACT_UTE[Region] ),
    "Select a value"
)

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Display lowest Hierarchy slicer field selected

@Alrythmond 

 

May be.. something like this.

Checking if the lowest level is filtered>>then next upper level>>then next

See the attached sample file as well

 

Measure =
SWITCH (
    TRUE (),
    ISFILTERED ( Table1[Level3] ), CONCATENATEX ( VALUES ( Table1[Level3] ), [Level3], "," ),
    ISFILTERED ( Table1[Level2] ), CONCATENATEX ( VALUES ( Table1[Level2] ), [Level2], "," ),
    ISFILTERED ( Table1[Level1] ), CONCATENATEX ( VALUES ( Table1[Level1] ), [Level1], "," )
)

View solution in original post

Alrythmond Regular Visitor
Regular Visitor

Re: Display lowest Hierarchy slicer field selected

Thank you for the reply,

 

It worked perfectly thank you!

Alrythmond Regular Visitor
Regular Visitor

Re: Display lowest Hierarchy slicer field selected

Hi @Zubair_Muhammad ,

 

I tried your formula and tweaked it, I came up with a problem.

ask3.PNG

it works when I select region, and the following hierarchy but when I select 20014 it doesn't appear, but when I select employee it does appear

Super User
Super User

Re: Display lowest Hierarchy slicer field selected

@Alrythmond 

 

Could you share your file?

 

Did you include all four levels in the formula?

Alrythmond Regular Visitor
Regular Visitor

Re: Display lowest Hierarchy slicer field selected

Hi @Zubair_Muhammad 

 

Yes I included all the levels

I will share you the file now, I can't share files right now because I am still new but here is the access to the google drive I saved it in

https://drive.google.com/file/d/1WzIcG_ouKfWHKp3s6eotE99-GKgvrofm/view?usp=sharing

Super User
Super User

Re: Display lowest Hierarchy slicer field selected

@Alrythmond 

 

I see that order of levels 2 and 3 in your MEASURE was not correct.

 

Use this MEASURE

 

Selectedslicer =
SWITCH (
    TRUE (),
    ISFILTERED ( FACT_UTE[Employee] ), SELECTEDVALUE ( FACT_UTE[Employee] ),
    ISFILTERED ( FACT_UTE[SG_Code] ), SELECTEDVALUE ( FACT_UTE[SG_Code] ),
    ISFILTERED ( FACT_UTE[OC] ), SELECTEDVALUE ( FACT_UTE[OC] ),
    ISFILTERED ( FACT_UTE[Region] ), SELECTEDVALUE ( FACT_UTE[Region] ),
    "Select a value"
)

View solution in original post

Alrythmond Regular Visitor
Regular Visitor

Re: Display lowest Hierarchy slicer field selected

Wow! Thank you so much @Zubair_Muhammad  you're  a life saver, it worked great I failed to see my mistake thank you!

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 162 members 1,781 guests
Please welcome our newest community members: