cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
RafalK
Advocate IV
Advocate IV

Dynamic formatting of measures - tutorial

Hi everyone - I would like to share how to use dynamic formatting on measures in Power BI.

This has already been available by using FORMAT DAX function, however that function converts measures to text and so it could not be used on most visuals.

 

Here is the final result that we will get. With a selection of a measure the formatting will change (2 decimal places for Value and 0 for Volume):

RafalK_0-1597435473380.pngRafalK_1-1597435483621.png

I will be working on a simple sales table with data:

RafalK_2-1597435551211.png

 

Tutorial - step by step:

1. Create a table with measures that you would like to select on the report. Add additional column that describes the desired measure format (you can use any format string that you would normaly use in the FORMAT() DAX function)

RafalK_4-1597435708673.png

 

2. Create a Dynamic measure with switch. 

 

RafalK_3-1597435616132.png

 

3. Add a slicer and add your Measure column to it so that we can select the measure we wish to see. Select a random value in the slicer.

11.png

 

4. Create a visual and add a desired Dimension (in my case Country) and Dynamic Measure as Value.

10.png

 

You will now be able to swap the measure on the graph based on the selection from the slicer but both measures still have the same format. 

 

5. Now we get to the hard part. You will need to add a Calculation Group using Tabular Editor.

You need to download and install it and then you will see it on the "External Tools" ribon in Power BI. 

RafalK_5-1597435824403.png

Open Tabular Editor and create a new Calculation Group. I have called mine Dynamic formatting.

Add new Calculation Item. I have called mine Format Measure.

In the Expression Editor add SELECTEDMEASURE()

In Format String Expression add SELECTEDVALUE('Measure'[Format], SELECTEDMEASUREFORMATSTRING())

This will take the measure used on the visual and format it by the definition from 'Measure'[Format]

Annotation 2020-08-14 221446.png

 

Click Save Changes and return to Power BI

 

6. Include Dynamic formatting in the graph. You can do it in two ways:

a. Add the Name from Dynamic formatting to graph legend (I had some problems with graph colors with this aproach)

3.png

b. Add Name from Dynamic formatting to visual filters and just select the only value

4.png

 

That's it. 

-- UPDATE -- 


This solution can generate issues on the report.

1. Once this is implemented, all of your numerical column will loose the "aggregate icon" in the model.

RafalK_0-1614937577995.png

This behaviour will persist even when you set the column aggregation to SUM or any other aggregation method.

 

You will not be able to simply drag and drop them to a visualization. A measure based on that column needs to be created first and then used in the visual.

 

I have also had a problem with creating a "Line and stacked column chart". Adding both - chart and line ended up with a Power BI error. 

 

Regards

Rafał Kun
11 REPLIES 11
rkreutziger2
New Member

Thank you so much for this! Is there a way for you to make certain fields "ignore" the formatting. I.E. the user selects "Sales" and there is a meausure ($), growth ($), and % Growth (%). I want the formatting to apply to the first two measures, but have the Growth % Ignore the formating. 

 

To deploy, i followed step 6b. Add Name from Dynamic formatting to visual filters. Therefore I tried to use ALL in the Growth % calculation, but it is still recognizing the filter. 

Hi, I met the same problem as you - cannot make this way of formatting to ignore some of the fields. Have you had solution for this?

Laurence21
Frequent Visitor

Hi Rafal,

 

Thanks so much for posting that! It has been really helpful and has been the first time I have used Tabular Editor. I have no idea how the solution worked but my report is looking very sleek now!
Best wishes, Laurence

pdelia
Frequent Visitor

Despite the limitations, this is by far the most useful approach to the selectable measure formatting problem I've seen. Every other tutorial on the topic involves setting static measures and/or formatting assignments, where this leaves it fully dynamic and based entirely on an easy adjustable table.

Mond
Helper III
Helper III

Hi Rafal,

 

Thanks for your solution.

I tried all the steps laid out 1 by 1 but it didn't work out with option 6 (b).

I am using Line and Clustered Column chart.

 

Do you have any new updates for this kind of charts.

Appreciate your help.

 

Thank you.

Remco1986
Helper I
Helper I

Awesome sulution! This helped me A LOT! I was using the Switch Measure option for 53 different metrics, some needed to be displayed as 1, some as 1.1, some as 1.11, some as 1.1% and some as 1.11% (you get the idea).

 

With this solution I'm now able to display every metrics in it's own format! 

 

However, is this approach also working for formatting? 

TaraB89
Helper II
Helper II

Hello, Thank you for the guide, it helped me with a visual where I was using a Dynamic Measure.

But....

It seems to have an effect on a lot of other visuals as well. Some measures suddenly take a different format then what I set it to.

Those are measures that have nothing to do with the ones I made it for. Sorting options dont work properly anymore because of it.

When I remove the Dynamic Formating table, it goes back to normal.

 

Simple example, I have a different measure that is a Percentage, but as a Gauge it simply won't show it as a percentage. In a matrix or table, it shows it as percentage, but not as a gauge.

 

How do i make it so that the Dynamic Formatting only works for the Dynamic Measure?

Hi - yea there are some problems with the method. 

For 1 it works best if you have just one measure on the visual. If you add more they will all be formated.

As for your Gauge I'm not sure wht it is the case. Perpaps the format you are using is not the same "percentage" format as the visual requires? All I can suggest here is to use the original measure without the dynamic format option.

Rafał Kun

Hi thanks for the reply.

 

Oh well it still helps knowing there is a work around 🙂 

Hope they will add something in the future so you can use FORMAT and that it wont be text or something

Thanks for the guide!

DJBAJG
Helper III
Helper III

Do you have the .pbix for this? I'm trying something very similar but I'm not sure if it's going to work and my dataset is very large making experimentation on it extremely difficult. Thanks. 

katekul
Frequent Visitor

Great idea, thank you for sharing with us! 🙂

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors