Using Measure as Legend

[daysolix12]

@All @Greg_Deckler @lbendlin @amitchandak  I created a dynamic category measure that is dependent on date filters. I would like to generate a line plot for trend analysis using this category measure as a legend. Unfortunately, powerbi does not accept measures as legends. Creating a calculated column does not help with my problem because it only gives a static value as at a particular date e.g today and uses that category for a particular row all year round. To put in context, I would like to display tenure of employees in a company per tenure category. Tenure category is expected to be dynamic for every increasing year and cannot be a static value for all years.

Any ideas on how to achieve this please?

[daysolix12]

@xifeng_L , thank you so much for helping out. Not sure what you've provided helps. I'm looking to add more detail and hopefully you might understand what I'm trying to achieve.

I have the proposed legend table below called TenureCategory

The dax below calculate Employee tenure as a function of specified/filtered date

EmployeeTenure =

    DATEDIFF(

        MAX(MergeSup_Assgn[Person.Date Start]),

        SWITCH(

            TRUE(),

            ISBLANK(MAX(MergeSup_Assgn[Person.Actual Termination Date])), MAX(Dates[Date]),

        -- MAX(Person[Date Start]) > MAX(Dates[Date], TODAY()),

            MAX(MergeSup_Assgn[Person.Actual Termination Date]) > MAX(Dates[Date]), MAX(Dates[Date]),

            MAX(MergeSup_Assgn[Person.Actual Termination Date]) < MAX(Dates[Date]), MAX(MergeSup_Assgn[Person.Actual Termination Date]),

            MAX(Dates[Date])  // Assuming intended to use the Dates[Date] as fallback

            ),

        MONTH)/12

My measure for calculating the tenure category below...

Dynamic Tenure Category =

VAR CurrentTenure = [EmployeeTenure]  // Assume this measure calculates the dynamic tenure based on selected date

RETURN

    CALCULATE(

        MAX('TenureCategory'[Tenure Category]),

        FILTER(

            TenureCategory,

            CurrentTenure >= TenureCategory[Min Years] &&

            CurrentTenure < TenureCategory[Max Years]

        )

    )

I would like to generate a plot that shows the employee turnover rate by different tenure categories over several years. It is expected that the tenure increases every year, the EmployeeTenure Dax helps with this. How do you suggest I can achieve this pls. @lbendlin @Greg_Deckler  @amitchandak 

[lbendlin]

Tenure category is expected to be dynamic for every increasing year and cannot be a static value for all years.

No need to automate this. Manually change the reference table when needed. Maintain it on a SharePoint for example.

[xifeng_L]

Hi @daysolix12 ,

You can try below solution.

#1. Create an auxiliary table to use as a legend field. Such as:

//New Table Expression

LegendTable = 
DATATABLE(
    "Item",STRING,
    "Index",INTEGER,
    {
        {"Measure1",1},
        {"Measure2",2},
        {"Measure3",3}
    }
)

#2. Create a dynamic measure that can calculate different measures based on different legend items.

//Measure Expression

AutoIndicator = 
VAR CurIndicator = SELECTEDVALUE('LegendTable'[Item])
RETURN
SWITCH(
    CurIndicator,
    "Measure1",[Measure1],
    "Measure2",[Measure2],
    "Measure3",[Measure3]
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~