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

• 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 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:

• 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.