Getting value from Hierarchical slicer

[Anonymous]

Hi All,

Need your help.

I am using a hierarchical filter which is using three fields for hierarchy.

Country 

     State

        City

Can we have all the levels from the hierarchy in the Legend area for a Line graph

For example, if country is selected then legend should be selected country, all states from selected country , all cities from selected state. As shown below, in the Hierarchical slicer we have selections USA --> Alabama --> Huntsville three lines in the Line graph to represent selected country, state and city

Please let me know how can we acheive this.

Thanks

[Anonymous]

@Anonymous 

I don't think there is a native method implemented in Power BI, however, I feel like this can be achieved in a very very indirect way.

Let's say the origin data is something like this

Country       State              City            Value      Year

USA	Alabama	Huntsville	20	2019
USA	North Carolina	Raleigh	30	2019
USA	Alabama	Bessemer	15	2019
USA	Alabama	Huntsville	5	2018
USA	North Carolina	Raleigh	15	2018
USA	Alabama	Bessemer	10	2018

From there we create another table that serves as the legend of the visual

_Legends = UNION(
                SUMMARIZE(Origin,Origin[Country],"Type","Country"),
                SUMMARIZE(Origin,Origin[State],"Type","State"),
                SUMMARIZE(Origin,Origin[City],"Type","City")
            )

we end up with this

USA	Country
Alabama	State
North Carolina	State
Huntsville	City
Raleigh	City
Bessemer	City

Now let's define a measure that reflects the value corresponding to each level in the dimension table

Measure = VAR
             _area = FIRSTNONBLANK(_Legends[Country],[Country])
          VAR 
            _legend = FIRSTNONBLANK(_Legends[Type],[Type])
        RETURN
                SWITCH(_legend,
                    "Country",CALCULATE(SUM(Origin[Value]),FILTER(Origin,Origin[Country]= _area)),
                    "State",CALCULATE(SUM(Origin[Value]),FILTER(Origin,Origin[State]=_area)),
                    "City",CALCULATE(SUM(Origin[Value]),FILTER(Origin,Origin[City] = _area)),
                BLANK())

With that being said, attributes which are not included in the filtered table would return blank in the measure defined above (and are by default not visible to the end-user)

When a country is selected ...

when a country is selected

When a state is selected ...

when state is selected

[Anonymous]

Hi @Anonymous ,

Thanks for looking into it.  I am able to get all the levels in the hierarchy in the "legends", as shown in your solution. 

But i have noticed, we dont see the independant values for each level. For example, If we select Country = USA and State= Alabama, then we should see measure value = 75 for USA and 35 for Alabama (as expected solution). But we see measure value = 35 for USA, because once we select the state = Alabama, it filters out all the results for Country, USA

Please advise if there is any solution for this.

Many thanks.

[Anonymous]

Hi @Anonymous ,

Thanks for your reply.

I have followed all the steps that you mentioned. After that i get a blank graph.

May i please request you to share the .pbix file.

Thanks.

[Anonymous]

Hi @Anonymous ,

I wasn't able to figure out a way to upload documents to this forum, so please try the link below to see if you could access the sample via google drive

https://drive.google.com/file/d/1vUyWfRGJL0C19jGIGM3-kUkn-IXGFzax/view 

[Anonymous]

Hi @Anonymous 

Thanks for your reply.

I tried the link, it gives me following error.

[Anonymous]

I tried using another account to access the file and saw a similar error page for preview (not sure if it is a network error or something) - however, I was able to download the file on clicking the "download" button

[Anonymous]

Hi @Anonymous 

My Legend table look like below. May be Blank graph is due to blank values in Country column. Any idea?

[v-lionel-msft]

Hi @Anonymous ,

Do you mind uploading your PBIX file?

If you can, please upload your PBIX file.

Best Regards,
Lionel chen

[Anonymous]

Thanks @Anonymous .

I am able to download this file. I have followed the same steps but still get the "blank" graph, not sure why 😞

Do you have any idea?

Thanks again.

[v-lionel-msft]

Hi @Anonymous ,

I tried to help you to realize your idea, but result is not satisfactory.

Maybe you can submit your ideas to Power BI Ideas 

Best regards,
Lionel Chen

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

[Anonymous]

Thanks @v-lionel-msft 

it seems, this is not possible in Power BI. I will post this idea.

Thanks

[v-lionel-msft]

Hi @Anonymous ,

I found a Hierarchical slicer in MarketPlace, maybe it can help you:

Best regards,
Lionel Chen

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

[Anonymous]

Thanks @v-lionel-msft for your reply.

I am already using the Hierarchical slicer, imported from market place. there are three columns used for the slicer, thus creates a hierarchy. Issue is, how can we have all the three fields in the Legend.

thanks.

[v-lionel-msft]

Hi @Anonymous ,

Did @Anonymous 's answer solve your problem?

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards,
Lionel Chen