Dynamic Legends using Field Parameters that are also dynamic based on Slicer selections.

[edy80y]

I have a Power BI chart showing numeric results (e.g. sales) by Location.  I have slicers at the top of the report for Location and Team.

Im trying to use field parameters to dynamically change the legend of the chart, but i want the field parameter to also be dynamic based on slicer selections.

For example:

- When the Location and Team slicers are not selected, then Chart legend is by Location

- When More than one Location is selected, then Chart legend are the selected Locations

- When One Location is selected and no Team is selected, then Chart legend are the teams within the Location

- When One Location is selected and multiple Teams are selected, then Chart legend are the selected Teams

- When One Location is selected and one Team is selected, the Chart legend are the sales reps within the selected team.

I created the field paramater as per usual, and edited the code as per the example below, and dragged it into the 'Legend' field of the chart.

There are no errors showing in Power BI but, the behaviour of the report is not working as intended.  The field parameter seems to only detect the first 2 scenarios from the behaviour i listed above.

Hoping to get some guidance on how to make this work?? Cheers!

===========================================================

BELOW IS THE LOGIC USED FOR THE FIELD PARAMETER

===========================================================

legend_LocationTeamAgent = 

 {

    ("_CustomLegend", 

SWITCH (

TRUE,

// Site = 0 AND Team = 0 THEN Site

IF(ISFILTERED(QueryField[LOCATION]), COUNTROWS(VALUES(QueryField[LOCATION])),0) = 0 &&

IF(ISFILTERED(QueryField[TEAM]), COUNTROWS(VALUES(QueryField[TEAM])),0) = 0, 

NAMEOF(QueryField[LOCATION]),

// Site = 1 and Team = 0 THEN Team

IF(ISFILTERED(QueryField[LOCATION]), COUNTROWS(VALUES(QueryField[LOCATION])),0) = 1 &&

IF(ISFILTERED(QueryField[TEAM]), COUNTROWS(VALUES(QueryField[TEAM])),0) = 0, 

NAMEOF(QueryField[TEAM]),

    // Site > 1 and Team = 0 THEN Site

IF(ISFILTERED(QueryField[LOCATION]), COUNTROWS(VALUES(QueryField[LOCATION])),0) > 1 &&

IF(ISFILTERED(QueryField[TEAM]), COUNTROWS(VALUES(QueryField[TEAM])),0) = 0, 

NAMEOF(QueryField[LOCATION]),

    // Site > 1 and Team > 1 THEN Team

IF(ISFILTERED(QueryField[LOCATION]), COUNTROWS(VALUES(QueryField[LOCATION])),0) > 1 &&

IF(ISFILTERED(QueryField[TEAM]), COUNTROWS(VALUES(QueryField[TEAM])),0) > 1, 

NAMEOF(QueryField[TEAM]),

    // Site = 1 and Team = 1 THEN Agent 

IF(ISFILTERED(QueryField[LOCATION]), COUNTROWS(VALUES(QueryField[LOCATION])),0) = 1 &&

IF(ISFILTERED(QueryField[TEAM]), COUNTROWS(VALUES(QueryField[TEAM])),0) = 1, 

NAMEOF(QueryField[AGENT]),

    // Site = 0 and Team = 1 THEN Agent 

IF(ISFILTERED(QueryField[LOCATION]), COUNTROWS(VALUES(QueryField[LOCATION])),0) = 0 &&

IF(ISFILTERED(QueryField[TEAM]), COUNTROWS(VALUES(QueryField[TEAM])),0) = 1, 

NAMEOF(QueryField[AGENT]),

    // Site = 1 and Team > 1 THEN Team

IF(ISFILTERED(QueryField[LOCATION]), COUNTROWS(VALUES(QueryField[LOCATION])),0) = 1 &&

IF(ISFILTERED(QueryField[TEAM]), COUNTROWS(VALUES(QueryField[TEAM])),0) > 1, 

NAMEOF(QueryField[TEAM]),

    // Site > 1 and Team = 1 THEN Agent

IF(ISFILTERED(QueryField[LOCATION]), COUNTROWS(VALUES(QueryField[LOCATION])),0) > 1 &&

IF(ISFILTERED(QueryField[TEAM]), COUNTROWS(VALUES(QueryField[TEAM])),0) = 1, 

NAMEOF(QueryField[AGENT]),

    // Site > 0 and Team > 1 THEN Agent

IF(ISFILTERED(QueryField[LOCATION]), COUNTROWS(VALUES(QueryField[LOCATION])),0) = 0 &&

IF(ISFILTERED(QueryField[TEAM]), COUNTROWS(VALUES(QueryField[TEAM])),0) > 1, 

NAMEOF(QueryField[AGENT])

) // end of SWITCH

,0)

}

[OwenAuger · Accepted solution]

Hi @edy80y 

If you want a field parameter to switch dynamically based on conditions like this, you need to come up with a measure that is used to filter the field parameter.

The field parameter table itself is static (on refresh of the dataset) so the various conditions cannot be included in the DAX code defining the field parameter table.

I have attached a small example using the same table/column names you posted to illustrate.

Steps to replicate:

1. Create a field parameter with the three possible fields for the axis:

legend_LocationTeamAgent = {
    ("LOCATION", NAMEOF('QueryField'[LOCATION]), 0),
    ("TEAM", NAMEOF('QueryField'[TEAM]), 1),
    ("AGENT", NAMEOF('QueryField'[AGENT]), 2)
}

2. Create a measure that will be used to filter the field parameter table in the relevant visual.

I called this Legend Filter. The code is similar to what you posted, but each branch of SWITCH returns the unqualified name of the column, which corresponds to the first column of the field parameter.

This measure returns 1 if the current field parameter should be used, otherwise 0.

Legend Filter = 
VAR RequiredField =
    SWITCH (
        TRUE ( ),
        // Site = 0 AND Team = 0 THEN Site
        IF ( ISFILTERED ( QueryField[LOCATION] ), COUNTROWS ( VALUES ( QueryField[LOCATION] ) ), 0 ) = 0
            && IF ( ISFILTERED ( QueryField[TEAM] ), COUNTROWS ( VALUES ( QueryField[TEAM] ) ), 0 ) = 0, "LOCATION",

        // Site = 1 and Team = 0 THEN Team
        IF ( ISFILTERED ( QueryField[LOCATION] ), COUNTROWS ( VALUES ( QueryField[LOCATION] ) ), 0 ) = 1
            && IF ( ISFILTERED ( QueryField[TEAM] ), COUNTROWS ( VALUES ( QueryField[TEAM] ) ), 0 ) = 0, "TEAM",

        // Site > 1 and Team = 0 THEN Site
        IF ( ISFILTERED ( QueryField[LOCATION] ), COUNTROWS ( VALUES ( QueryField[LOCATION] ) ), 0 ) > 1
            && IF ( ISFILTERED ( QueryField[TEAM] ), COUNTROWS ( VALUES ( QueryField[TEAM] ) ), 0 ) = 0, "LOCATION",

        // Site > 1 and Team > 1 THEN Team
        IF ( ISFILTERED ( QueryField[LOCATION] ), COUNTROWS ( VALUES ( QueryField[LOCATION] ) ), 0 ) > 1
            && IF ( ISFILTERED ( QueryField[TEAM] ), COUNTROWS ( VALUES ( QueryField[TEAM] ) ), 0 ) > 1, "TEAM",

        // Site = 1 and Team = 1 THEN Agent
        IF ( ISFILTERED ( QueryField[LOCATION] ), COUNTROWS ( VALUES ( QueryField[LOCATION] ) ), 0 ) = 1
            && IF ( ISFILTERED ( QueryField[TEAM] ), COUNTROWS ( VALUES ( QueryField[TEAM] ) ), 0 ) = 1, "AGENT",

        // Site = 0 and Team = 1 THEN Agent
        IF ( ISFILTERED ( QueryField[LOCATION] ), COUNTROWS ( VALUES ( QueryField[LOCATION] ) ), 0 ) = 0
            && IF ( ISFILTERED ( QueryField[TEAM] ), COUNTROWS ( VALUES ( QueryField[TEAM] ) ), 0 ) = 1, "AGENT",

        // Site = 1 and Team > 1 THEN Team
        IF ( ISFILTERED ( QueryField[LOCATION] ), COUNTROWS ( VALUES ( QueryField[LOCATION] ) ), 0 ) = 1
            && IF ( ISFILTERED ( QueryField[TEAM] ), COUNTROWS ( VALUES ( QueryField[TEAM] ) ), 0 ) > 1, "TEAM",

        // Site > 1 and Team = 1 THEN Agent
        IF ( ISFILTERED ( QueryField[LOCATION] ), COUNTROWS ( VALUES ( QueryField[LOCATION] ) ), 0 ) > 1
            && IF ( ISFILTERED ( QueryField[TEAM] ), COUNTROWS ( VALUES ( QueryField[TEAM] ) ), 0 ) = 1, "AGENT",

        // Site = 0 and Team > 1 THEN Agent
        IF ( ISFILTERED ( QueryField[LOCATION] ), COUNTROWS ( VALUES ( QueryField[LOCATION] ) ), 0 ) = 0
            && IF ( ISFILTERED ( QueryField[TEAM] ), COUNTROWS ( VALUES ( QueryField[TEAM] ) ), 0 ) > 1, "AGENT"
    )
VAR CurrentField = MAX ( legend_LocationTeamAgent[legend_LocationTeamAgent] ) -- assume single selection

RETURN
    INT ( RequiredField = CurrentField )

3. Place the field parameter column on the axis of the visual.

4. Add a visual-level filter on the field parameter column, set to Top 1 of legend_LocationTeamAgent by Legend Filter. This filter will force the field parameter to be filtered to the correct column reference.

5. Now the visual's axis will change appropriately as selections are made on LOCATION and TEAM slicers.

e.g. If one LOCATION and one TEAM are selected, the axis field will be AGENT.

See the attached PBIX for reference.

Does this work for you?

Regards

[Elscc]

Hi Owen, this looks great, but I have a slightly different requirement, however when I have been asking copilot for assistant it appears to be copying your solution from this thread.

I am tracking complaints. I have a column chart that shows different measures by using a parameter. These measures are simple things like open per month, closed per month. Easy stuff.

What I am struggling with is that when looking at closed complaints I also want a legend to be applied, based on column values. But i do not want this legend for all the measures within the parameter. 

So i need a dynamic legend based on the measures selected via a parameter in the y axis. (my x axis is also a parameter, but just date groups).

I hope this makes sense and is reasonable to ask as a reply to OPs similar but different question.

Any advice? Thanks

[OwenAuger]

Hi @Elscc 

Yes, this is possible by making use of relationships between field parameters.

I have mocked up an example using this type of method, where there are three measures on one field parameter (Sales, Quantity, Average Unit Price) but the Product Category legend is only present for Quantity.

PBIX attached.

Here's how I set it up:

1. Create Field Parameters for both Measure Selection and Legend Selection.

2. Note that the Measure Selection field parameter table includes a copy of the Measure Selection column called Measure Selection Copy.

3. Create a table Measure Legend Bridge that contains the combinations of Measure and Legend you want to be displayed, but use an invalid value when you want no legend to be displayed (I used "None"):

4. Create a table Legend containing a single column of distinct values of Legend

5. Create relationships as follows. There are some constraints on how the relationships must be set up with field parameters:

6. Create a visual with:

• 'Measure Selection'[Measure Selection] as Y-Axis
• 'Legend Selection'[Legend] as Legend.
• Required field on X-Axis (I used a 'Calendar Selection' field parameter):

7. Now the visual works as follows:

Hope that helps! 🙂

[Elscc]

What a hero! Exactly what I needed. I really appreciate you taking the time to help.

[edy80y]

Owen, this is exactly what I was looking for, thank you!!

[amitchandak]

@edy80y , I doubt as of now you can control the axis using selectedvalues.

That can only happen when using unpivoted table

Refer: https://youtu.be/Qxvkg7fZrhU

[edy80y]

Thanks for the advise, but I dont think thats going to resolve my issue.

We already know that field parameters can be used to modify a chart legend, I want to take advantage of that but go one further by controling the behaviour of the field parameter based on whether or not slicers were used.

1. Slicer controls field parameter (field parameter configured with only 1 entry)

2. Field parameter used as the chart legend