Reply
Frequent Visitor
Posts: 11
Registered: ‎12-27-2016

Present different level of hierarchy according to a slicer

[ Edited ]

Hello everyone,

 

I have the following data:

 

Destination:

  • LATAM (h1)
    • Region1(h2)
      • Country1(h3)
      • Country2(h3)
    • Region2(h2)
      • Country3(h3)
      • Country4(h3)
  • ASIA (h1)
    • Region3(h2)
      • Country5(h3)
      • Country6(h3)
    • Region4(h2)
      • Country7(h3)
      • Country8(h3)

 

With the destination hierarchy presented above (which only Highest level of the hierarchy is in a slicer -h1-), I have a Matrix with destination on columns.

 

Basically, I need that when nothing is selected on the slicer, only h1 level is presented. But when any item on that slicer is selected, only h2 level is presented on the columns on that same matrix.

 

I think this is done using a calculated column but I'm not quite sure, I'm a PowerBi amateur!

 

In advance, thanks for any help!

 

David

Established Member
Posts: 221
Registered: ‎02-18-2016

Re: Present different level of hierarchy according to a slicer

Here are the component pieces that you need to understand:

 

1. Definitely this - ISFILTERED() - https://www.powerpivotpro.com/2013/03/isfiltered-a-better-way-to-detect-totals/

2. Maybe this - disconnected slicers - i can't find anything great on this ... looking

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast Smiley Happy
Frequent Visitor
Posts: 11
Registered: ‎12-27-2016

Re: Present different level of hierarchy according to a slicer

[ Edited ]

Hey @austinsense!

 

I already tried using the ISFILTERED(), but it is not working properly. I have a new column depending on the value of that filtered, something like this:

 

This measure is on the original destination table but refering to the disconnected table:

  • Measure = IF(ISFILTERED(DisconnectedDestination[H1]),1,0) 

This calculated column is on the original destination table:

  • Column = IF([Measure]=0 , Destination[H1], Destination[H2])

 

Despite that measure works perfectly when using the disconnected destination on a slicer (changes between 1 and 0 depending on filtering), the calculated column do not. 

 

Any tip here?

 

Thanks,

David

 

 

Highlighted
Moderator
Posts: 9,438
Registered: ‎03-06-2016

Re: Present different level of hierarchy according to a slicer

Hi @ortizd,

 

I noticed you mentioned two tables DisconnectedDestination and Destination. Would you please share some sample data and desired results for our analysis?

 

Best Regards,
Qiuyun Yu

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