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

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

Need Help

OwenAuger

2 years ago

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

Dynamic Legend.pbix25 KB

OwenAuger

Super User

2 years ago

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"):