SELECTEDVALUE Format Strings not working for Chart Axis

[ajohns3]

It seems like using SELECTEDVALUE in a measure dynamic format string is behaving strangely when that measure is used on an axis in any of the native chart types. SELECTEDVALUE([Field]) is picking a value (seemingly the first value based on selected sort order) from the available values when [Field] has more than one value in the current context.

As an example, consider the following situation: 

This table of fruit data is loaded:

This measure is defined:

Fruits Available = SUM('Table'[Number of Fruits])

with the following format string:

"#,0 " & SELECTEDVALUE('Table'[Fruit Name], "Multiple Fruit Types")

I would expect this measure to return "10 Multiple Fruit Types" when no filter is applied. And it does, when used in a Card visual. The issue enters when it is used in a chart visual:

In this example, I would expect the Y axis to read "1 Multiple Fruit Types, 2 Multiple Fruit Types..." etc - like the card at the top of the page. Why does it not behave like this?

The expected behaviour is observed when using the slicer; it's only the default option that seems broken:

Any help appreciated.

Thanks,

Adam

[v-dineshya · Accepted solution]

Hi @ajohns3 ,

Thank you for reaching out to the Microsoft Community Forum.

This is a known limitation in Power BI, dynamic format strings when used with SELECTEDVALUE() and when the measure is plotted on chart axes like Y-axis, X-axis, or secondary axis.

Please try below alternative workarounds.

1. Use a calculation group for formatting, Create a format calculation item that handles display logic separately.

SELECTEDVALUE('Fruit Table'[Fruit Name], "Multiple Fruit Types")

And assign that as a dynamic string expression in a calculation group.

2. You can use an intermediary variable.

Fruits Available =
VAR _value = SUM('Table'[Number of Fruits])
VAR _fruit = SELECTEDVALUE('Table'[Fruit Name])
RETURN
_value

Format string:

VAR _fruit = SELECTEDVALUE('Table'[Fruit Name])
RETURN
"#,0 " & IF(NOT(ISBLANK(_fruit)), _fruit, "Multiple Fruit Types")

Note: This doesn’t fix the axis issue in all visuals, but helps make behavior consistent in tables/cards.

3. If you are expecting user communication not formatting logic, consider leaving the measure format as static , and use a dynamic text label like card/visual title instead.

Title_Fruits =
"Fruits Available - " &
SELECTEDVALUE('Table'[Fruit Name], "Multiple Fruit Types")

Then show that in the chart title or subtitle dynamically. This keeps the axis numeric.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya · Accepted solution]

Hi @ajohns3 ,

Thank you for reaching out to the Microsoft Community Forum.

This is a known limitation in Power BI, dynamic format strings when used with SELECTEDVALUE() and when the measure is plotted on chart axes like Y-axis, X-axis, or secondary axis.

Please try below alternative workarounds.

1. Use a calculation group for formatting, Create a format calculation item that handles display logic separately.

SELECTEDVALUE('Fruit Table'[Fruit Name], "Multiple Fruit Types")

And assign that as a dynamic string expression in a calculation group.

2. You can use an intermediary variable.

Fruits Available =
VAR _value = SUM('Table'[Number of Fruits])
VAR _fruit = SELECTEDVALUE('Table'[Fruit Name])
RETURN
_value

Format string:

VAR _fruit = SELECTEDVALUE('Table'[Fruit Name])
RETURN
"#,0 " & IF(NOT(ISBLANK(_fruit)), _fruit, "Multiple Fruit Types")

Note: This doesn’t fix the axis issue in all visuals, but helps make behavior consistent in tables/cards.

3. If you are expecting user communication not formatting logic, consider leaving the measure format as static , and use a dynamic text label like card/visual title instead.

Title_Fruits =
"Fruits Available - " &
SELECTEDVALUE('Table'[Fruit Name], "Multiple Fruit Types")

Then show that in the chart title or subtitle dynamically. This keeps the axis numeric.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[ajohns3 · Accepted solution]

Thanks @v-dineshya,

I tried out your second solution. The calculation item solution I would like to avoid as I do make use of implicit measures.

The format string you suggest 

VAR _fruit = SELECTEDVALUE('Table'[Fruit Name])
RETURN
"#,0 " & IF(NOT(ISBLANK(_fruit)), _fruit, "Multiple Fruit Types")

does seem to produce the desired result.

I have also found that adding an ALLSELECTED() filter to the format string also gives the desired result. 

"#,0 " & CALCULATE(SELECTEDVALUE('Table'[Fruit Name], "Multiple Fruit Types"), ALLSELECTED())

 I expect there are situations where these approaches will diverge.

[Bmejia]

Hi,
I am trying to understand what you are really trying to accoumplish here.  If you are using SUM, it is going to sum the number of fruits.  If you don't have anything selected it will sum 10.  If you are looking to have like "1 Multiple Type" when selecting Apples or Bananas or if you select both "2 Multiples" then you need to use Distinct Count on the Fruite Name.

Fruit Multiple=CALCULATE(DISTINCTCOUNT('Table'[Fruit Name])) &" Multiple Fruit Types"

If you want a mix of both you can use something like this.  If you select one or two it will give you  the "Multiples..", if clear the filter it will sum the fruit.

Fruit Available =

 IF(ISFILTERED('Table'[Fruit Name]),

 CALCULATE(DISTINCTCOUNT('Table'[Fruit Name])) &" Multiple Fruit Types"

 ,[Fruits Available])

Thanks

[ajohns3]

I am not trying to sum the types of fruit, this is a formatting issue involving the dynamic format string and SELECTEDVALUE().