Power BI Dynamic Legend

Question

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.

Answer 1

Let's say I have some population data on the largest 20 US counties as follows:

Data = DATATABLE(
       "Rank", INTEGER, "Region", STRING, "County", STRING, "State", STRING,
       "2010", INTEGER, "2012", INTEGER, "2014", INTEGER,
       {{1,"West","Los Angeles","California",9818605,9826773,9826773},
        {2,"Midwest","Cook","Illinois",5194675,5199971,5199971},
        {3,"South","Harris","Texas",4092459,4109362,4109362},
        {4,"South","Maricopa","Arizona",3817117,3824834,3824834},
        {5,"West","San Diego","California",3095313,3105115,3105115},
        {6,"West","Orange","California",3010232,3017598,3017598},
        {7,"South","Miami-Dade","Florida",2496435,2503034,2503034},
        {8,"Northeast","Kings","New York",2504700,2508515,2508515},
        {9,"South","Dallas","Texas",2368139,2375207,2375207},
        {10,"Northeast","Queens","New York",2230722,2233895,2233895},
        {11,"West","Riverside","California",2189641,2202978,2202978},
        {12,"West","San Bernardino","California",2035210,2042027,2042027},
        {13,"West","King","Washington",1931249,1937157,1937157},
        {14,"West","Clark","Nevada",1951269,1953927,1953927},
        {15,"South","Tarrant","Texas",1809034,1816850,1816850},
        {16,"West","Santa Clara","California",1781642,1786267,1786267},
        {17,"South","Broward","Florida",1748066,1752122,1752122},
        {18,"South","Bexar","Texas",1714773,1723561,1723561},
        {19,"Midwest","Wayne","Michigan",1820584,1815246,1815246},
        {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:

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

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:

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

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:

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

---

Here's a link to the PBIX file I created for this: Variable Legend.pbix