Use DAX to Show Values in any Way You Like! Dynamic Format Strings in Power BI

[@alicedwonderland7733]

Bas, fantastic video as always! Note to my fellow watchers: remember to first go to the report Options and enable this preview feature. As for a use case, how about when you have a beautiful switch between Amounts and Quantity? This is what I'm using it for. Amounts must be in thousands, and Quantity in, well, regular formatting. And for thousands, I used "#,##0,.k", to see the 'k' at the end. Update: fudge, the formatting is a mess when we use it in a visual (the thousands one) and apply data labels! It's all over the place!

[@PeterFabian-gu1lp]

Thank you mate, I've struggled with formatting for half a day, you saved my @$$ and won a new follower! ;)

[@Mircor55]

Amazing video! Your workaround for measure driven labels solved my problem!

[@LeighRadmore]

Thanks for this - we had a user requirement to show a measure value in K or M based on the the length or value itself and I just made a way to do this automatically with dynamic formatting - wouldn't have known about it if not for this video.

[@paulgallagher2987]

I was thinking about this too. Did you do something along the lines of "IF [Sales] > 800,000 then M formatting else K formatting"

[@LeighRadmore]

@@paulgallagher2987 yea basically, I used switch as there were multiple 'buckets'

[@sholder9933]

Marco Russo just published a video on how to do this.

[@digitsletters972]

Thank you! Regarding dynamic formatting - will try it in matrices, I often use dynamic columns with some additional calculations following a set of periods (e.g. % variance of last period vs the previous)

[@HowtoPowerBI]

not sure if i get the use case .. could explain more?

[@digitsletters972]

I have a matrix with values containing sales by period (need number formatting), rows containing customers, columns containing not only periods but some additional calculations which demand different formatting: variance vs previous period(%), absolute variance (+-), weight of sales by customer in total sales (%), etc. I solved it by using a combination of SWITCH/SELECTEDVALUE/FORMAT() directly in a measure, but it has some inconveniences for users (while exporting to excel the columns with formatting are stored as text) and for me (additionally complicates my multi-row measure🙂). Hope I will be able to put that SELECTEDVALUE/FORMAT part into the dynamic format

[@paulgallagher2987]

Great video as always Bas! Thanks a lot. And just when I'm about to need to do multi currency reporting. Please could you share the DAX for your MaxMin Measure. I don't understand how it outputs 2 different values. Is it a TRUE/FALSE output? eg MinMax = IF(OR( [Sales] = Maxvalue, [Sales] = MinValue), 1,0)

[@ruioliveira4149]

Thank you very much for these video. Was exactly what I was searching for. You are great!!

[@giorgiopresicce8192]

I found it incredibly useful, as always! Thanks for consistently updating all of us with the latest and most innovative content! I truly appreciate it!

[@patitosamsungtelita]

Hola Bas, Eres un crack. Mil gracias por compartir tu sabiduría. Abrazo fuerte

[@markhenderson3771]

Thanks for the content. Is there a way to add color to dynamic formatting? For example, I am in a table column and I want to use a UNICHAR arrow and make the up arrow (9650) green and the down arrow (9660) red, all within the dynamic formatting.

[@vishalnasre1251]

This is really out of the Box Thanks Bas for great video.

[@Cleanfreak005]

Hi Bas, super helpful and very well explained video. May you please add a video, with option allowing only currency symbol to be in bold and the figures as normal. Thank you

[@walteradamsbe]

Hi Bas, great tutorial (like always). I have a use case to have different formatting for every row from where I work: we have a sales table visual which shows a number of rows of sales actuals (in EUR) with right below it (in the same table) the number of units sold (of course in absolute values). It's not my preferred visual to do this because I find it confusing but management is used to this table so there you go 🙂

[@kshitijwadhwani5077]

Thanks a lot super simple and understandable explanation , I did watch few videos but none comes across the way you explain and make it simple to understand. I had a request can you make a video to explain how we do all the advance filtering in a filter pane , doing those same things in slicer ?

[@HowtoPowerBI]

thanks a lot! :) .. depends a bit what type of filter you are looking for .. not all of them are possible with a slicer in a similar way

[@Jworth-kc4qc]

Hey Bas! You are a life saver man. One question, will this work for KPI Card displaying either decimal, percentage, or whole number for value and target? Thanks and keep it up!

[@JurgensvanEeden]

Awesome video excited to try it out. Do you know if this will work for a visual that uses a field parameters those different values. At the moment i end up when some of the measures returns small amounts it wout show 0,0k.

[@alicedwonderland7733]

Thank you, Bas! You make my work shine!

[@HowtoPowerBI]

Glad to hear this thx for the support!

[@anneetaylor464]

Thanks, especially for sharing the dimCurrency Table :)

[@macilakertous8041]

Hi Bas, great video as always. can you show me what the minmax measure looks like please!?

[@sebaphilip]

this is amazing!!! is that currency table available somewhere? Thks

[@HowtoPowerBI]

There is a download link in the description

[@nishantkumar9570]

Awesome! Thank You so much! A request: Could you please share lights on % YOY, % MOM, %WOW, WTD using new window ,offset functions. Thank you again.

[@HowtoPowerBI]

thanks! here you go :) kzbin.info/www/bejne/m5rFZotrrbefsJo

[@nishantkumar9570]

@@HowtoPowerBI I rarely miss any of your videos, I already watched it and it helped me a lot in understanding those new function and it's implementation. Still I'm little bit confused on Month over month change and Week over week change.

[@davemurdoch5687]

Hi Bas, great channel. Great videos. As a suggestion : I would love to see your version of reconciliation. In quantity and financial value maybe even with currencies. Accounts Receivables also very interesting. Thanks for the inspiration.

[@theminimalistings]

Awesome! Thank you Bas! 🙌 Btw, do you mind sharing the the [MaxMin] dax? 😅

[@brunavogt278]

Hello Bas, awesome video as always... one doubt, do you know how I could format a specific column of a matrix so only the values of that column are with a bold font?

[@marc-andreperreault1353]

Hi Bas, Great content as usual! I have a use case, where I present a line chart and I'm switching between different measure (sales, units, baskets, etc.). The dynamic option for my calculations works fine, but the Y axis labels is not always following. Is there a way to make sure the Y axis also displayed the good units ? Thank you!

[@user-po2ze4mj4m]

Very helpful as always you do. Thank you Bas. Is it possible to display different currency values with Thousands, Millions, Billions.

[@No_Saints_]

Thanks for the video. I do have a request where the format string would be based on the count of digits in a number would be a specific format (IE. If the value is less than 1M show $#0.0K, if the value is less than 1K show $#0, etc.)

[@datactive859]

when I was noticed with april update earlier this month, I knew Bas is going to give this lecture. Thanks bro, your version is the best

[@HowtoPowerBI]

thank! lol -> what's gonna be my next vid?! ;)

[@unnikrishnansanthosh]

if its BAS then something out of the box, great thanks for sharing

[@harrietdrums]

I didn't know that you had to go into options and enable preview features in order to do this, and was wondering/googling for ages as to why I didn't have dynamic as a formatting option despite being on the most recent verion of PowerBI. Please can you specify this for beginners like me 😭

[@vishalnasre1251]

Hi Bas, One question, Is it recommended to use these preview feature for our production report ? if yess, could there be potential damage to report in case Power BI removes that feature ?

[@tofonofo4606]

Great Stuff Bas! My first thought was to use a Len() function of some sort to determine the value length and then dynamically adjust the value display across a single table without the slider needed. You could put all the same currency logic also. Helpful if you have values in millions and thousands in the same column. Cheers!

[@HowtoPowerBI]

thanks a lot :) yea, definitely also possible, just depends on whether you want that slicer. And also if you have many options (for example many currencies) probably the table is a bit easier.

[@arbazahmad7177]

Really Amazing...Thanks for this..

[@edwinarchico439]

As always, excellent Bas, thank you!

[@HowtoPowerBI]

thank you very much for watching again !! :D

[@kathimmaculata]

Hi Bas, in my case the values has to be in $ and % . Is that possible?

[@nemnoch]

Curious if you can use they for dates formats as well. Been struggling to figure it out on my own.

[@TheEdgarGL]

In the April version of the P-BI, I don't find the option to "Add more analysis to your look". On the MS blog I didn't find any technical note that talks about it. Do you know if it's available elsewhere or if it's a bug in this version?

[@K-mk6pc]

I found it increadibly useful.

[@HowtoPowerBI]

Awesome!

[@JohnOttoKnoke]

Great video as always, thanks. I'm working on a similar report that has several currency formats. Can share with us the URL where you got the different currency formats, please? Much appreciated

[@HowtoPowerBI]

thank you! I've put the excel file in the download for this video :)

[@nishanth4323]

@@HowtoPowerBI 👍👏

[@ngoduyvu]

Amazing video as usual

[@vesnastojanova]

Thank you. Realy good case.

[@juanignacioXI]

Hello Bas, can u give me a TIP in how you build the MAXMIN measure? I suppose u used variables like this: Var MaxValue = MAX('YourTableName'[YourValueColumnName]) Var MinValue = MIN('YourTableName'[YourValueColumnName]) Return IF( "Measure" = MAXVALUE, MAXVLUE, IF( "Measure" = MINVALUE, MINVALUE, BLANK())) Can u please correct me if im wrong, thanks in advance.

[@joeyjojojrshabadoo]

how about using Dynamic formating Strings (i didn't check if this was already suggested sorry) to "suppress" small number count. e.g. if the resulting value is greater then zero but less then 15 display a "suppressed" else value .... great content btw bud!! keep it up

[@HowtoPowerBI]

thanks for watching! :) i was thinking about scenarios like that as well, but I probably would then just filter those small values out instead of showing a different format .. or ?

[@joeyjojojrshabadoo]

@@HowtoPowerBI you could do that but you would be losing information in say, data tables when displayed, its suppressed data not null or lack of data, no? thoughts?

[@ajaaskelainen]

Right on top! Great stuff

[@arbazahmad7177]

Great explanation.. But how to up or down arrow in data label

[@konradjonsson1]

Hi Bas. As always, great videos! I have a requirement where the report should show a)the total sales for each country, and b)the total sales for the region. A region is a group of countries. Each region has its own reporting currency defined, e.g. Europe = EUR, Americas = USD, etc. Finally, the Group total is the sum of all regions, displayed in one Group-currency. Your video explains a way to show each country’s currency code - do you also have a solution for how to show Region subtotals and Group totals?

[@HowtoPowerBI]

you would need to adapt/extend the logic of the dax expression for the formatting string .. check if not isfiltered(dimRegion[region]) then the group currency otherwise check which reigon is selected -> selectedvalue([dimRegion[region])="EMEA" then euro

[@John-cq6vh]

Can I sort the dynamic format asc or desc? What's best way to do this? thanks

[@theshakoone]

Bas, what if I don't want to format into strings but keep the values. However, I need to show either a decimal, currency, int, or percent based on user selection on a matrix. Is that possible with dynamic?

[@HowtoPowerBI]

sure :) its like the unit example -> build a table that contains decimal, currency, int, pct in a column. use that for a slicer. Use dynamic format to see which one is selected and return the values with the format you like.

[@theshakoone]

@@HowtoPowerBI thank you. I currently have the data type in the dim and am trying to change the data type on the line chart that is displayed. I will keep trying workarounds.

[@In_a_state]

Using the formatting option to show complex labels instead of having to use calculation groups (which could be often problematic depending on where else you used the same measure or the axis) is the bigger update for me.

[@HowtoPowerBI]

Its still a bit of cluncky work around. :) an fx button to set label values would b nice

[@Barrett-WB]

Did PowerBI remove this option? I have the latest version and don't see Dynamic as an option in the format dropdown

[@HowtoPowerBI]

go to options, preview features, turn on dynamic format strings for measures

[@Barrett-WB]

@@HowtoPowerBI Thanks!

[@theA12d1]

thanks!!

[@royhj8966]

Brilliant!

[@HowtoPowerBI]

Thx Roy!

[@kenekholm8325]

Can we do calculations in the dynamic format string, like lookup exchange rate and then format?

[@HowtoPowerBI]

Yes

[@thuytamdang3390]

Million thanks!

[@HowtoPowerBI]

thank you for watching! :)

[@luisfernandomacedo1451]

Great Bass!

[@HowtoPowerBI]

Thanks 😊

[@perfectionist6624]

as a powerbi developer do we need to learn Power apps & power Automate ? how Power apps & power Automate will benefit for PBI developer.

[@johnkennethonet-makaticand2590]

😆 "Filipino currency symbol" - Lol. Philippine Peso. Very useful, Bas.

[@Thalapraga]

Hi, please let me know how to add the slider values in the table like in this video

[@bobert13581]

It's a bad practice to do a cross filter=both just so the slicers get filtered. Just apply an 'is not empty' filter on a measure to the slicer visual instead. Microsoft says this in their bidirectional relationships guidance page and it's super easy and quick to do.

[@RichardOliver]

I use one I saw Alberto Ferrari do so, in all my models, there is a measure called 'The Alberto' that I use for this purpose.

[@HowtoPowerBI]

yea that would be better :) thx for pointing it out

[@nidhi5823]

What if on last tread graph , I want to show negative number in red font and in brackets.. only negative numbers.. how to achieve that

[@pakarussiapinto6718]

Awesome thank you

[@HowtoPowerBI]

thx!!! :D

[@narendrababu2287]

😍

[@garudabowo]

can someone explain about the four quotation marks functioned?

[@crizzy4u764]

Thanks Bas 😍

[@HowtoPowerBI]

thx for watching :D

[@momopeaches]

Is there a way I can format percentages without %? For example, if the result is 51.3%, I want to show only 51.3, not 0.513.

[@luispecem]

Need help! Matrix visuals are very very slow. I've put the dynamic format for 10 measures.

[@vishwassharma3312]

👍👍❤️❤️

[@TamNguyen-cy3ix]

Hi. Thanks for this! How to show it as 100M? e.g. 900,000,000 will give me 9 instead of 0.9B or 900M.

[@HowtoPowerBI]

kzbin.info/www/bejne/fIfNpYqlqqlofNk

[@obagoriolastephen300]

What do you do when you have values with Km , L and mpg units in the same column. Sample....

[@henryconmorales7348]

del lujo....

[@HowtoPowerBI]

gracias !

[@raeesg]

Nice 👍

[@HowtoPowerBI]

thank you!

[@scandinavia_adventure]

❤️

[@nishanth4323]

Bas

[@TheInfernalwing]

how to work on between numbers and %?

[@menkar91]

Colombia 💪

[@HowtoPowerBI]

Netherlands 💪🙃

[@zxccxz164]

but how do you do this if you use SSAS?

[@vishwassharma3312]

Kindly provide the video of Google Data Studio.

[@HowtoPowerBI]

maybe one day

[@imperia81]

норм но плохо что нет на русском языке))) все красиво но ничего не понятно))))