Present different level of hierarchy according to a slicer

[ortizd]

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

[austinsense]

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

[ortizd]

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

[v-qiuyu-msft]

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