Dynamic Legend

[KristofferS]

I currently have a monthly trend clustered column chart that sorts the measured values by each calendar month. There are 2-4 column values for each month as they are "categories" put in the Legend of the visual. Within each category there is more sorting into "subcategories". I have a slicer that allows the user to choose a specific category, but I cannot figure out a way to show the subcategory when the category slicer is used. In essence, I am looking for a dynamic Legend that changes based from category to subcategory based on whether the slicer is used or not. If I design the visual with the subcategory as the Legend and the user chooses not to use the category slicer, the visual becomes a mess of hundreds of columns per month.

[clewis]

Hi There,

I watched this youtube video on dynamic legend which was very helpful, (https://www.youtube.com/watch?v=8e8a3o1w51M) but i'm stuck on the variable measure and getting an error saying "syntax for userrelationship is incorrect.  Here is my formula.....

Count of Unique Teammate Swipes (VARIABLE CATEGORY) =

SWITCH(

    True(),

    -----------------------ELT Category--------------------------

    SELECTEDVALUE('Legend Categories'[Field]) = "ELT Leader",

    CALCULATE(

        [Count of Unique Teammate Swipes]

        USERELATIONSHIP('Legend Categories'[Category], 'Teammate_WLI_Designation'[ELT Leader.])

    ),

    -----------------------WLI Designation--------------------------

    SELECTEDVALUE('Legend Categories'[Field]) = "Designation",

    CALCULATE(

        [Count of Unique Teammate Swipes]

        USERELATIONSHIP('Legend Categories'[Category], 'Teammate_WLI_Designation'[WLI Designation])

    ),

    -----------------------Work Location--------------------------

    SELECTEDVALUE('Legend Categories'[Field]) = "Work Location",

    CALCULATE(

        [Count of Unique Teammate Swipes]

        USERELATIONSHIP('Legend Categories'[Category], 'Teammate_WLI_Designation'[Work Location])

    ),

    BLANK()

)

I set up my legend category table as follows....

Legend Categories =

DISTINCT(Union(

    SELECTCOLUMNS(

        'Teammate_WLI_Designation',

        "Category", 'Teammate_WLI_Designation'[WLI Designation],

        "Field", "Designation"

    ),

    SELECTCOLUMNS(

        'Teammate_WLI_Designation',

        "Category", 'Teammate_WLI_Designation'[ELT Leader.],

        "Field", "ELT Leader"

    ),

    SELECTCOLUMNS(

        'Teammate_WLI_Designation',

        "Category", 'Teammate_WLI_Designation'[Work Location],

        "Field", "Work Location"

    )

))

All of my legend variables are coming off one table, so i created 3 inactive relationships from WLI_Integration_Designation table columns aligning as called out above to legend categories table [category].

Can anyone help me figure this out??  Thank you in advance!

[Anonymous · Accepted solution]

Hi @KristofferS 

You can refer to the following solution.

Sample data

Then create a field paramater

Then put the paramater to the legend field

Then you can select the paramater to choose display cat/subcat legend

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.