How to dynamically set the format of a measure in a Power BI report?

[EylesIT]

I have a Power BI report with a measure that returns the value of other measures depending on the selected value of a slicer, as per the DAX below:

Slicer Measure = 
    SWITCH(
        LASTNONBLANK('Available Measures'[MeasureName], "")
        , "Sales", [Sales]
        , "Orders", [Orders]
        , "Orders Completed %", [Orders Completed %]
    )

This allows a user to select which measure they want to see in a line chart visual.

However, when the user selects Orders Completed %, I want the [Slicer Measure] to be formatted as a percentage. But I cannot work out a way to specify the measure format dynamically. 

I have tried formatting the Orders Completed % expression to a percentage text like this

Slicer Measure = 
    SWITCH(
        LASTNONBLANK('Available Measures'[MeasureName], "")
        , "Sales", [Sales]
        , "Orders", [Orders]
        , "Orders Completed %", FORMAT([Orders Completed %], "Percent")
    )

but when selected the Orders Completed % does not display anything at all.

Any suggestions on how to do this?

[MFelix]

Hi @EylesIT ,

When you use the format option on a measure or calculated column the value that you apply the format becomes a text value so this is what is happening in your chart, that is why it does not show on the line chart since when it read the values it's a text and cannot be represent in the visualization.

In this case and since you cannot show values in percentage and values by using the switch measure I believe you have one of two options:

• Make your final value multiplied by 100 and then place a card with the text values in % or Currency
• Another option is to create bookmarks and 3 different charts

Check both in attach PBIX file.

[daxer]

To be able to do this THE RIGHT WAY you have to use calculation groups which can be created only via Tabular Editor. It can't be done easily via the PBI interface. But if you do it thorugh the groups it'll work exactly the way you want - the measure will be dynamically formatted on the fly and it'll still be a number, not a piece of text. For how to do this, please refer to YT or www.sqlbi.com.

[EylesIT]

We are still on version SQL 2016 which does not support calculation groups. Is there a way to achieve the same functionality in SSAS 2016?

[MFelix]

Hi @EylesIT ,

You are using direct query mode?

[EylesIT]

The Power BI report connects to the SSAS database in "Connect Live" mode.

[MFelix]

Hi @EylesIT ,

Following @daxer  answer check the video below that was publish today with this exact situation.

[EylesIT]

This video does not achieve what I have asked. The video shows measures being displayed as text values by converting the number to a text string and adding some more after the number. 

What I want to achieve is to dynamically change the way Power BI formats a numeric measure depending on a a slicer value.

[daxer]

@EylesIT 

You don't understand what the video really is all about. Alberto shows how to dynamically format a measure and the fact that he uses strings to add the unit to the number has nothing to do with this. Calculation groups let you format a measure's value DYNAMICALLY using the SELECTEDMEASUREFORMATSTRING() function. Try to watch this video once again and think about what Alberto does and how it can be used to format a number dynamically. Calculation items have properties and these properties can be set at runtime. One of the properties is the format string.

https://docs.microsoft.com/en-us/dax/selectedmeasureformatstring-function-dax

[EylesIT]

Perhaps it does, but the version of SSAS I am using is 2016, so this method is not available to me, unfortunately.

[MFelix · Accepted solution]

Hi @EylesIT ,

Even in live connection you can use calculation groups and create this changes, follow the step below:

• Assuming you already have Sales Measure
• On external tools execute the Tabular editor

• Rigth Click on Tables
• New Calculation Group

• Rename as you need
• Rigth click
• New Calculation Item

• Rename it sales and write the folllowing code:

SELECTEDMEASURE()

• Add two new calculation items with the formulas you have in the Power BI for the other two measures, if your measure in Power BI is for example: DIVIDE(SUM(Table[Column]), SUM(Table[Column2]) this is the formula you must place on the calculation item.
• On the Percentage formula make your formatting as %

• Save the model

• Go back to Power BI and refresh the model you should see your new table in the model
• Now create your line chart with the following setup:.
  • Axis: Column you need
  • Values: Measure Sales
  • Legend: Column from the calculated group table
• Slicer: Column from calculated group table

Check result below:

I cannot send you a copy of my file since it's on direct query.  But believe this can help you achieve the needed steps.

[Quiny_Harl]

Hi @MFelix 

Is this solution possible if your data is with import mode?

Also, I do not see the external tools tab in my ribbon. Do you know what could be the reason for that?

[MFelix]

Hi @Quiny_Harl ,

Yes this option is possible also in import mode, for the external tools to work you need to install them it's

ALM Toolkit

DAX Studio

Tabular Editor