0 votes
1 view
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 in BI by Chris (11.1k points)
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !