Forum Discussion

Dave82's avatar
Dave82
Frequent Visitor
4 years ago

Dynamic Legend not working

I have a REEL_QUALITY table with a Date Hierarchy as the X-Axis and a raw Result value as the Y-Axis, typically broken out into monthly averages.  I currently have the Legend field set to the Plant column in the same table, and it works as intended, but I also need to allow the end user to pick a different grouping between Plant or Line or Platform or Aggregate columns.  All these columns reside in the same source table, I just need to provide a way for the end user to switch the column that is doing the legend grouping by using a drop down slicer.

 

It seems like all the videos on dynamic legends (https://www.youtube.com/watch?v=8e8a3o1w51M) are all using multiple fact/dimension tables but none show how to dynamically switch the legend grouping on a single table.

 

I tried making a metadata table with my 4 different category types:

 

LegendCategories = 
DISTINCT( 
    UNION(
        SELECTCOLUMNS(REEL_QUALITY, 
        "Type", "Plant",
        "Value", REEL_QUALITY[Plant]
        ),
        SELECTCOLUMNS(REEL_QUALITY, 
        "Type", "Line", 
        "Value", REEL_QUALITY[Line]
        ),
        SELECTCOLUMNS(REEL_QUALITY, 
        "Type", "Platform", 
        "Value", REEL_QUALITY[PlatformType]
        ),
        ROW("Type", "Aggregate", "Value", BLANK())
    )
)

 

 

Gives me the appropriate legend meta data:

Type

Value
PlantNew York
PlantGreenville
Line1A
Line2A
PlatformLegacy
Platform3rd Gen
Platform4th Gen
Aggregate (no legend grouping)

 

Then I tried to do a measure for the dynamic legend column selection to be bound to the legend field on the line chart:

 

Selected Legend = 
SWITCH(TRUE(),
SELECTEDVALUE(LegendCategories[Type]) = "Plant", REEL_QUALITY[Plant],
SELECTEDVALUE(LegendCategories[Type]) = "Line", REEL_QUALITY[Line],
SELECTEDVALUE(LegendCategories[Type]) = "Platform", REEL_QUALITY[Platform],
SELECTEDVALUE(LegendCategories[Type]) = "Aggregate", -- no legend column
)

 

 

Obviously I know this above syntax doesn't work.  I'm not sure how to tell it to select a different column.  I had an initial 'solution' that used 4 different tables with a different column on each legend using buttons/bookmarks, but that is really unwieldly and causes problems with other report needs.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Dave82 ,

    In order to provide you a suitable solution, could you please share some sample data in the table REEL_QUALITY and your expected result with backend logic and special examples? It is better if you can share a simplified pbix file. Thank you.

    In addition, please review the following video which has the similar requirement as yours. Hope it can help you get the solution.

    Power BI - Dynamic Columns in a Table

    1. Unpivot all columns except the [name] column

    2. Create a matrix visual(Note: The column with the field name "Column" in the figure below is the [Attribute] column from the previous step)

    Best Regards