Dynamically change the format of values in Power BI

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

Can you change the format of a measure or a value in Power BI dynamically? Is it possible to set a value to be a percentage sometimes, and sometimes to be a currency? can you change the currency format? The answer to all these questions is yes. In this short blog, I am going to show you how you can do it. There are many samples of this already available. As an example, Kasper de Jonge showed this technique a long time ago in his article here.

Static formatting in Power BI

One way to format values (either columns or measures) is by the format pane settings under the Column or Measure tools.

formatting for columns in Power BI

This method is the simple method that can work if you want to set the format for a column or measure.

However, sometimes, you want to do things more dynamically. For example, you might have users in different countries with different formatting requirements.

DAX Format function

The Format function is a simple and powerful function in DAX. This function can be used for generating some format options. The function can be used simply like this:

The first parameter of the format function is the value which we want the formatting to be applied on it, and the second parameter is the format of it. There are many formatting options available, which are suitable for number, date, and etc. Here is a good detailed guide about it;

Now the FORMAT function can be combined with some other methods to make the dynamic formatting a possible option.

Parameter table

Parameter table is a disconnected table from the rest of the model. This table can act like a parameter for other calculation. Here in the example below I showed how it can be used to select between measures:

Now these two methods, can work together to build a dynamic formatting.

Parameter table for currency

Here I have created a parameter table for the currency values. This table can be created anywhere; In Excel, or another data source, or even in Power BI Desktop.

Dynamic Format Measure

Then I created a measure that dynamically change the format using the value selected from the table above;

The code above is checking what value is selected from the Currency table (using the SELECTEDVALUE function), and then uses it inside a conditional expression and assign the format string of the equivalent currency to it (using the SWITCH function). Finally, this format string is used inside a FORMAT function to format the measure’s value.

The below screenshot is a glimpse of the result;

Taking it a few steps further

When you use DAX for making things dynamic, then you can always do amazing things. Here is an example:

I created two other tables, one for the Thousand separator;

And one for Decimal places;

And I used an expression like below;

This means that now we can have a dynamic formatting like below in Power BI. This allows enabling or disabling the thousand separator, removing or adding decimal places and currency change.

dynamic formatting in Power BI

Consideration

One important consideration of using this method is that the return value of your measure or column would be of the TEXT data type (within the format string defined).

Download Sample Power BI File

Download the sample Power BI report here:

Enter Your Email to download the file (required)

Video

Summary

DAX is the answer of making many things dynamic in Power BI. In this blog, you have seen how you can use the FORMAT function with the aid of some other functions such as SWITCH and SELECTEDVALUE to make the formatting of a field dynamically possible. Let me know in the comments below if you have a situation that you can or can’t apply this method and why.

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply