Explore Courses Blog Tutorials Interview Questions
0 votes
in BI by (17.6k points)

I'm working with a geographical hierarchy. I'd like for the Legend on my line or ribbon chart to show the next lowest relevant level of detail based upon a slicer selection.

GOAL: No Slicer Selection yields a Regional Chart (lines for Southeast, Northeast, etc). Select "Northeast" Region from Slicer yields a State Chart (lines for Maine, New Hampshire, etc.) Select "Maine" State from Slicer yields a County Chart, and so forth.

I can do this in a Card using a combination of HASONEVALUE and VALUES to obtain a dynamic single value. However, the Legend field of Ribbon or Line Charts will not accept such a measure.

Is there any way to create a Power BI Line or Ribbon chart with a dynamic Legend based upon slicer selection?

I'd rather not create a separate measure for all hierarchy nodes as there are thousands of possible leaves.

1 Answer

0 votes
by (47.2k points)
  • I have some population data on the largest 20 US counties as follows:


       "Rank", INTEGER, "Region", STRING, "County", STRING, "State", STRING,

       "2010", INTEGER, "2012", INTEGER, "2014", INTEGER,

       {{1,"West","Los Angeles","California",9818605,9826773,9826773},




        {5,"West","San Diego","California",3095313,3105115,3105115},



        {8,"Northeast","Kings","New York",2504700,2508515,2508515},


        {10,"Northeast","Queens","New York",2230722,2233895,2233895},


        {12,"West","San Bernardino","California",2035210,2042027,2042027},




        {16,"West","Santa Clara","California",1781642,1786267,1786267},




        {20,"Northeast","New York","New York",1585873,1587481,1587481}


  • Using this data as a source in the query editor, we will produce two tables. The first will be a Fact table that unpivots the years and looks like this for the top three counties:

Top 3 Counties

  • The second will be a Bridge table that's just a list of unique Region, State, County combinations:

Bridge Table

  • We'll create one final table using DAX. This will be our Legend table.

Legend = UNION(SUMMARIZECOLUMNS(Fact[Region], Fact[Rank], "Level", 1), SUMMARIZECOLUMNS(Fact[State], Fact[Rank], "Level", 2), SUMMARIZECOLUMNS(Fact[County], Fact[Rank], "Level", 3) )

  • Make sure the relationships are set on the Rank columns so that your relationship diagram looks like this:

Relationship Diagram

  • Now we'll write a measure that takes advantage of these levels that we just created in the Legendtable.

Measure = VAR StateCount = CALCULATE(DISTINCTCOUNT(Bridge[State]), ALLSELECTED(Bridge), ALLSELECTED(Legend)) VAR RegionCount = CALCULATE(DISTINCTCOUNT(Bridge[Region]), ALLSELECTED(Bridge), ALLSELECTED(Legend)) VAR LevelNumber = SWITCH(TRUE(), StateCount = 1, 3, RegionCount = 1, 2, 1) RETURN CALCULATE(SUM(Fact[Population]), Legend[Level] = LevelNumber)

  • Basically, we only want to show the population for the appropriate level.

  • Using this measure on a line chart with Legend[Region] in the legend box and Fact[Year] on the axis, the result looks like this when slicers are left blank:

No Selections

  • When we select a region and then a state, we get the following:


If you are preparing for the Power BI certification exam, then take up this Power BI training by Intellipaat that offers instructor-led training, hands-on projects, and certification.

Related questions

0 votes
1 answer
asked Dec 30, 2020 in BI by Chris (11.1k points)
0 votes
1 answer
asked Dec 14, 2020 in BI by Vamsee Krishna (22.5k points)
0 votes
1 answer
asked Mar 29, 2021 in BI by Chris (11.1k points)
0 votes
1 answer
asked Mar 28, 2021 in BI by Chris (11.1k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

30.4k questions

32.5k answers


108k users

Browse Categories