Dynamic Dual Y axis with Parameters & Legend in line chart

[as1195]

@PowerBI @dax @Visualization I need to create a line chart where both the primary Y-axis and the secondary Y-axis are dynamic based on parameter selections in two slicers, along with the legend selection. However, I'm encountering an issue where I can't add the legend after configuring both Y-axis results, or vice versa. How can I achieve this?
If its not feasible any custom visual will help to solve this?

[grazitti_sapna]

Hi @as1195 ,

In Power BI, configuring a dynamic dual Y-axis line chart with both axes and a dynamic legend based on slicers can be challenging because Power BI's native line chart visual currently has limitations in handling multiple dynamic Y-axes with legends. Here are a couple of approaches you could try:

1. Using Calculation Groups for Dynamic Measures

• Set up two calculation groups in Tabular Editor, each containing the possible measures for the primary and secondary Y-axes.
• Use slicers to control each calculation group, allowing users to switch between different metrics for each axis.
• For the legend, you can add another slicer to dynamically switch dimensions or categories if they are part of the same data table. However, be aware that this approach may still be limited in native Power BI visuals.

Here’s how you can implement it:
Step 1: Enable External ToolsTo create calculation groups, you need Tabular Editor, which is available as part of the external tools in Power BI Desktop. Ensure it is installed and enabled.

Step 2: Create Calculation Groups

1. Open Tabular Editor:
• In Power BI Desktop, go to External Tools and open Tabular Editor.
2. Create Calculation Groups for Y-Axes:
• In the left pane of Tabular Editor, right-click on Tables and select Create New Calculation Group.
• Rename the calculation group to something like Primary Y-Axis Measures.
3. Define Calculation Items for the Primary Y-Axis:
• Add a new calculation item for each measure you want to include on the Primary Y-axis. For example:
• Revenue → Add the formula for Revenue.
• Profit → Add the formula for Profit.
• For each calculation item, assign a Name and write the measure formula in the Expression Editor. For instance:
  
  DAX
  
  SELECTEDMEASURE()
4. Repeat the Process for the Secondary Y-Axis:
• Create a second calculation group called Secondary Y-Axis Measures.
• Add calculation items for measures like Units Sold, Conversion Rate, etc.

Step 3: Return to Power BI

1. Add the Calculation Groups to the Model:
• After saving the changes in Tabular Editor, the calculation groups will appear as new tables in your Power BI data model.
2. Create Slicers for the Calculation Groups:
• Drag the Name field from the Primary Y-Axis Measures and Secondary Y-Axis Measures calculation groups into slicers.
• These slicers will allow users to dynamically select the measures displayed on the Y-axes.

Step 4: Configure the Line Chart

1. Add the Primary Axis Data:
• Drag the Value field from Primary Y-Axis Measures into the chart’s Y-axis.
2. Add the Secondary Axis Data:
• Drag the Value field from Secondary Y-Axis Measures into the chart’s Secondary Y-axis.
3. Configure the Legend:
• If the legend is tied to a specific dimension (e.g., Region or Category), drag it into the Legend field.

Step 5: Handle Interaction Between Slicers and ChartEnsure that the slicers interact properly with the line chart:

• Go to Format Pane > Edit Interactions.
• Verify that the slicers are configured to filter the chart visuals.

Optional: Synchronize AxesIf needed, configure the Y-axes to use the same scale by setting Y-axis Start/End values manually in the Format pane.

TestingSelect different measures in the slicers and observe how the Y-axis values and lines on the chart update dynamically.

2. Custom Visuals for Enhanced Dual Y-Axis and Legend Control

Several custom visuals in the Power BI marketplace support dual Y-axes and allow for more flexibility with legends. Here are a few that might work well:

• Xviz Advanced Combo Visual: This visual offers dynamic dual Y-axis support, extensive legend configuration options, and better handling of multiple data series.  LINK
• Power BI Charticulator Visual: While a bit more complex, Charticulator can allow for more customization, including dynamic axis settings and legends based on fields selected in slicers. Link

If I have resolved your question, please consider marking my post as a solution. Thank you!

[as1195]

Hi @grazitti_sapna 
Thank you for taking your time to reply 🙂
I tried the solution you provided but unfortunately the solution dint work for me

[Anonymous · Accepted solution]

Hi @as1195 ,

I made simple samples and you can check the results below:

SelectedPrimaryYValue = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Parameter 2'[Parameter Order]) = 0, SUM('Table'[Percent]),
    SELECTEDVALUE('Parameter 2'[Parameter Order]) = 1, SUM('Table'[Number])
   
)
SelectedSecondaryYValue = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Parameter'[Parameter Order]) = 0, SUM('Table'[Sale]),
    SELECTEDVALUE('Parameter'[Parameter Order]) = 1, SUM('Table'[Cost])
   
)

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Kedar_Pande]

@as1195 

DynamicSales = 

SWITCH(

TRUE(),

SELECTEDVALUE(Slicer[Type]) = "Sales", [Sales],

SELECTEDVALUE(Slicer[Type]) = "Volume", [SalesVolume]

)

https://community.fabric.microsoft.com/t5/Desktop/dynamic-legend-based-on-slicer-selection-possible/...

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn