Line Graph visual with 2 selectors for Y- and secondary Y-Axis with CORRECT FORMAT

[cn4422]

Hi,

I have several metrics as measures, for example this one:

MA_CPC_Calc = DIVIDE([MA_Spend_Calc],[MA_Clicks_Calc],0)

What I want to accomplish is to bring those measures to the Y- and secondary-Y-Axis of a line graph visual and then being able to select which 2 metrics should be displayed (with a slicer).

Here's selector 1:

Dynamische_Metrik_01 =

SWITCH(

    TRUE(),

    SELECTEDVALUE('Metrik_Tabelle_01'[Metrik]) = "CPL", [MA_CPL_Calc],

    SELECTEDVALUE('Metrik_Tabelle_01'[Metrik]) = "CTR", [MA_CTR_Calc],

    SELECTEDVALUE('Metrik_Tabelle_01'[Metrik]) = "Leads", [MA_Conv_Calc],

    SELECTEDVALUE('Metrik_Tabelle_01'[Metrik]) = "CVR", [MA_CVR_Calc],

    SELECTEDVALUE('Metrik_Tabelle_01'[Metrik]) = "CPC", [MA_CPC_Calc],

    SELECTEDVALUE('Metrik_Tabelle_01'[Metrik]) = "Impressionen", [MA_Impress_Calc],

    SELECTEDVALUE('Metrik_Tabelle_01'[Metrik]) = "Kosten", [MA_Spend_Calc],

    SELECTEDVALUE('Metrik_Tabelle_01'[Metrik]) = "Klicks", [MA_Clicks_Calc],

    [MA_CPL_Calc]  -- Standardwert, falls keine Auswahl getroffen wurde

)

with the according table for the slicer visual:

Metrik_Tabelle_01 =

DATATABLE(

    "Metrik", STRING,

    {

        {"CPL"},

        {"CTR"},

        {"Leads"},

        {"CVR"},

        {"CPC"},

        {"Impressionen"},

        {"Klicks"},

        {"Kosten"}

    }

)

This works - however, since the inital format is being overwritten and I cannot (or don't know how) to put a format on the measures again, like "#,0.00" or "#,0.00 €" afterwards, values are being shown not formatted, which is not that great. 🙂

I also tried to work with calculation groups + slicer but here the problem is that one calculation group is overwritting the second one and I can only display one metric in the visual.

Any input on how to fix this or any other solution on how to display 2 different metrics (with a selector) in the line graph are very much appreciated. 😄

Thx!

[v-dineshya]

Hi @cn4422 ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.


If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[cn4422]

Hi @v-dineshya ,

thanks for your reply.

Here is the download for the Test PBI-File

(It's via dropbox, you don't need to register, just skip until the download-option is being shown).

I've already created the graph and everything.

What I want to accomplish is that in the graph the correct format is being displayed in the visual. For example, if "Costs" is being selected, it should display the currency format. If CTR is being selected, it should display the %-format, and so on...

In case that this is only possible with calculation groups, thats fine as well... but I couldn't manage to select values on both Y- and secondary-Y-Axis at the same time with calcualtion groups.

Thank you!

[v-dineshya]

Hi @cn4422 ,

Thank you for reaching out to the Microsoft Community Forum.

Use Calculation Groups:

The most scalable and formatting-friendly solution is using calculation groups, but the limitation is that only one calculation group can be evaluated at a time, which makes displaying two dynamic metrics on different axes tricky.

Workaround: Use Two Calculation Groups With Independent Axis Handling.

1. Create Two Independent Calculation Groups

You'll need to use Tabular Editor to do this (external tool). Create two calculation groups:

a. MetricSelectorY1
Column: MetricY1

Create calculation items like:

CPC = [MA_CPC_Calc]
CTR = [MA_CTR_Calc]
Kosten = [MA_Spend_Calc]
...

b. MetricSelectorY2
Column: MetricY2

With same calculation items as MetricSelectorY1. Now you have two separate selectors one for each axis.

2. Add a Format String Expression
In each calculation item (in Tabular Editor), define a format string expression.

SWITCH(
SELECTEDMEASURENAME(),
"Kosten", "#,0.00 €",
"CTR", "0.00%",
"CPC", "#,0.00 €",
...
"0.00"
)

This keeps formatting dynamic and follows what the user selects.

3. Use Two Slicers in the Report
Create two slicers using MetricSelectorY1[MetricY1] and MetricSelectorY2[MetricY2]. These control your two dynamic axes.

4. Add Both Measures to Your Line Chart
In your line chart: Use a shared Axis (e.g., Date)

Add:

SELECTEDMEASURE() from MetricSelectorY1 → this goes to Y-Axis

SELECTEDMEASURE() from MetricSelectorY2 → this goes to Secondary Y-Axis

Note: Power BI will allow dual-axis rendering only when there are exactly 2 measures.

Benefits: You retain native formatting (%, €, decimals). Fully dynamic dual-axis switching. Clean and scalable with additional metrics.

Note: Do not use a SWITCH inside a DAX measure if formatting is a concern calculation groups are better since they allow format string control. Tabular Editor is required to create calculation groups and assign format string expressions.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[cn4422]

@v-dineshya Thanks for your reply.

I think I have already tried your approach but it somehow didn't work correctly with the switching of measures. I tried it with a Dummy-Measure as a placeholder.

Anyways, I followed your instructions and created everything again, but I don't know what I should put in the Y- and secondary Y-Axis...

Here is the updated Power BI File.

It would be awesome if you could have another look. 🙂

[v-dineshya]

Hi @cn4422 ,

Please follow below steps.

1. Create Two Independent Measures for Y-Axis and Secondary Y-Axis
You already have Dynamische_Metrik_01 for the primary Y-axis. Create a second measure, Dynamische_Metrik_02, for the secondary Y-axis. Use two separate slicer tables to avoid conflicts.

2. Set Up the Model
Slicer Table for Y-Axis (Primary): You already have Metrik_Tabelle_01:

Metrik_Tabelle_01 =
DATATABLE(
"Metrik", STRING,
{
{"CPL"},
{"CTR"},
{"Leads"},
{"CVR"},
{"CPC"},
{"Impressionen"},
{"Klicks"},
{"Kosten"}
}
)

Note: Slicer Table for Secondary Y-Axis, Create a second table, Metrik_Tabelle_02, with the same structure. Ensure these tables are not related to each other or to other tables in the model to avoid unintended filtering.

3. Create the Second Dynamic Measure

Create a measure for the secondary Y-axis, similar to Dynamische_Metrik_01:

Dynamische_Metrik_02 =
SWITCH(
TRUE(),
SELECTEDVALUE('Metrik_Tabelle_02'[Metrik]) = "CPL", [MA_CPL_Calc],
SELECTEDVALUE('Metrik_Tabelle_02'[Metrik]) = "CTR", [MA_CTR_Calc],
SELECTEDVALUE('Metrik_Tabelle_02'[Metrik]) = "Leads", [MA_Conv_Calc],
SELECTEDVALUE('Metrik_Tabelle_02'[Metrik]) = "CVR", [MA_CVR_Calc],
SELECTEDVALUE('Metrik_Tabelle_02'[Metrik]) = "CPC", [MA_CPC_Calc],
SELECTEDVALUE('Metrik_Tabelle_02'[Metrik]) = "Impressionen", [MA_Impress_Calc],
SELECTEDVALUE('Metrik_Tabelle_02'[Metrik]) = "Kosten", [MA_Spend_Calc],
SELECTEDVALUE('Metrik_Tabelle_02'[Metrik]) = "Klicks", [MA_Clicks_Calc],
[MA_CPL_Calc]
)

4. Add Measures to the Line Graph

Add Dynamische_Metrik_01 to the Values field of the line graph (this will plot on the primary Y-axis).
Add Dynamische_Metrik_02 to the Secondary Values field (this will plot on the secondary Y-axis).
Place the Year-Week column on the Axis field of the line graph.

5. Set Up Slicers

Add two slicers to your report: One slicer using Metrik_Tabelle_01[Metrik] to control Dynamische_Metrik_01 (Y-axis).
Another slicer using Metrik_Tabelle_02[Metrik] to control Dynamische_Metrik_02 (Secondary Y-axis).

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[cn4422]

Hi @v-dineshya ,

thanks for your reply.

I've already done exactly what you suggested in my Power BI Testfile.

Being able to display the 2 measures with slicers is fine.

However, what does not work as already mentioned in my inital post is the thing with the correct format of the measures in the graph:

This works - however, since the inital format is being overwritten and I cannot (or don't know how) to put a format on the measures again, like "#,0.00" or "#,0.00 €" afterwards, values are being shown not formatted, which is not that great. 

Perhaps you have an idea on how to solve this as well? 😊

Thx in advance!

[v-dineshya]

Hi @cn4422 ,

Power BI does not allow dynamic formatting (like "#,0.00 €" or "0.00%") directly inside SWITCH() measures because a measure can only have one format string. Once you switch across multiple measures with different formats (currency, percent, integer), only the default format applies and it cannot dynamically change based on the selected value.

Solution: Use Calculation Groups with Dynamic Format Strings
This is the only robust way to have dynamically formatted values in a line chart while keeping formatting like €, %, etc. Here's how you can do it properly.

Please follow below steps.

Dynamic Metric Selection with Formatting Using Calculation Groups

1. Create a Calculation Group in Tabular Editor
Use Tabular Editor (external tool in Power BI Desktop) to create a Calculation Group called SelectedMetric.

Create a calculation group with these items:

Name Expression Format String Expression
CPC [MA_CPC_Calc] "#,0.00 €"
CPL [MA_CPL_Calc] "#,0.00 €"
CTR [MA_CTR_Calc] "0.00%"
CVR [MA_CVR_Calc] "0.00%"
Leads [MA_Conv_Calc] "#,0"
Impressionen [MA_Impress_Calc] "#,0"
Klicks [MA_Clicks_Calc] "#,0"
Kosten [MA_Spend_Calc] "#,0.00 €"

Note: Add a column in the calculation group for [Axis] to tag whether it's Primary or Secondary. That way you can use 2 independent slicers that control two separate calculation groups (or filter this one group twice using disconnected slicers).

2. Use Two Disconnected Slicers
Instead of two SWITCH measures, use the calculation group as the measure and apply it on the line chart directly. You'll need to add two separate calculation groups (Primary Metric, Secondary Metric) if you want to control both axes. In Tabular Editor, duplicate the first group and rename it accordingly.

3. Place the Calculation Group in the Visual
Use the calculation item from Primary Metric for the Y-axis. Use the calculation item from Secondary Metric for the secondary Y-axis. The correct formatting will automatically apply per item due to the Format String Expression.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[cn4422]

Hey @v-dineshya ,

thanks again for your effort and your help.

I tried to follow your steps, but couldn't really manage to - or at least it didn't work so that I could select the correct measures in the visual with the calc groups.

In my test-file I've set up two calculation groups:

MetricSelectorY1-sec and

MetricSelectorY2-prim

In addition I had to adjust the Calculation items like this to ensure that the correct measure in the visual is being selected:

 IF (SELECTEDMEASURENAME() = "Dummy_Selected Value Prim", [Costs], SELECTEDMEASURE())

IF (SELECTEDMEASURENAME() = "Dummy_Selected Value Second", [CPC], SELECTEDMEASURE())

 ...

I've also added the respective format strings, like this: 

"#,0.00 €"

On the Y- and secondary Y-Axis i placed two dummy-measurse:

Dummy_Selected Value Prim = SELECTEDMEASURE()

Dummy_Selected Value Second = SELECTEDMEASURE()

The model now works, however, in the visual the correct format is still not being shown. Only for the Y (prim) but not for the Secondary Y (sec).

For testing purposes I've created two simple card visuals where everything works fine.

But I don't know how to get it right in the line graph visual. 😐

Here's the current version of my Test File.

[v-dineshya · Accepted solution]

Hi @cn4422 ,

Power BI only applies format strings from calculation groups to the primary Y-axis. For the secondary Y-axis, the format string from the calculation item is ignored.

Please find below Workaround options.

Option 1: Use separate visuals stacked

If you absolutely need formatting on both axes: Split the visual into two overlaid line charts (transparent background) one for the primary, one for the secondary metric. Sync axes carefully to make the experience seamless. Apply the formatting in each visual since each will have its own Y-axis.

Option 2: Format manually with a helper measure (for cards or tooltips)

Use the formatting string inside the DAX for Dummy_Selected Value Second for card visuals or labels:

Dummy_Selected Value Second (Formatted) =
VAR val = [Dummy_Selected Value Second]
VAR met = SELECTEDVALUE('Metrik_Tabelle_02'[Metrik])
RETURN
SWITCH(
TRUE(),
met = "Kosten", FORMAT(val, "#,0.00 €"),
met = "CPC", FORMAT(val, "#,0.000 €"),
met = "CTR", FORMAT(val, "0.00%"),
FORMAT(val, "#,0.00")
)

Note: Use this only where formatting matters (cards, tooltips), since formatted strings cannot be plotted on Y-axes.

Please refer community threads  and Microsoft articles .

Create calculation groups in Power BI - Power BI | Microsoft Learn

Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn

Calculation groups in Analysis Services tabular models | Microsoft Learn

Solved: Format a Calculation Group Item - Microsoft Fabric Community

Use custom format strings in Power BI Desktop - Power BI | Microsoft Learn

Note: If you still want to represent formatting string for both Y-axis and secondary Y-axis with line chart.

I’d recommend submitting this as a feature request in the official Power BI Ideas forum:
https://ideas.fabric.microsoft.com/

The Product team actively reviews suggestions there, and if others in the community upvote it, it could be considered for future updates.

I trust this information proves useful. If it does, kindly “Accept as solution” and give it a 'Kudos' to help others locate it easily.
Thank you.

[cn4422]

Hi @v-dineshya ,

alright - thanks for the additional tips and the clarification regarding format of Y- and secondary Y-Axis!