Forum Discussion
Dynamic Legends using Field Parameters that are also dynamic based on Slicer selections.
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
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
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! 🙂
What a hero! Exactly what I needed. I really appreciate you taking the time to help.