DAX Fridays! #32: FORMAT

[@RobertoStaltari]

These videos are little gems! Thanks Ruth

[@supersayan9888]

Hi Ruth, Thank you so much for sharing these videos. I did not know the possibilites with the Format function. So just learned something new today. I am on my way to view the next video :D.

[@CurbalEN]

Perfect! Glad ir was useful!

[@CesarCavalli]

Muchisimas gracias, como cada video donde se aprende tanto en todo lo que enseñas . Te agradezco mucho.

[@CurbalEN]

Muchas gracias a ti por el feedback y buen fin de semana! /Ruth

[@CesarCavalli]

@@CurbalEN buen finde para ti también! 🦋

[@horizon3919]

You are making our life easy with the KT. Thanks!

[@CurbalEN]

My pleasure! /Ruth

[@NorbertoVeraReatigaNVR]

Gracias Ruth, excelente detalle de la función, cada set de datos trae sus propias particularidades y con este variado ejemplo nos da la herramienta para resolver cada caso...

[@CurbalEN]

+Norberto Vera Reatiga De nada Norberto! Ahora utilizo el archivo pbix para ver cómo formatear mis fórmulas , en vez de usar la documentation, más rápido! :) Saludos! /Ruth

[@lydiaaidyl3328]

Your videos are always there to save my day!

[@CurbalEN]

Yey 🎉🎉 /Ruth

[@SmartBimson]

Very interesting and helpful video ! Thanks Ruth ! Have a great weekend 👍🏻🎉🌞🌞🌞🌞🌞

[@CurbalEN]

+SmartBimson Have a great weekend too Andy 🏝!

[@SmartBimson]

Will be more 🏌🏼with a lot of 🌞 ... spring is coming !

[@CurbalEN]

+SmartBimson Not here...yet... so enjoy it for me! 😄 /Ruth

[@Victor-ol1lo]

As always great video Ruth !! I really had no idea what FORMAT is able to manage, as my usage was limited to dates. Thanks a lot. Thumbs Up !!

[@CurbalEN]

+Victor Friesen Thanks Viktor! Have a great weekend :) /Ruth

[@armenia670]

Thank you so much, you finish well my weekend 😀

[@CurbalEN]

Excellent 👌

[@messinaalejandro]

From Argentina congratulations excellent videos

[@CurbalEN]

+Alejandro G. Messina Hola Alejandro, Muchas gracias y buen fin de semana! :) /Ruth

[@TheAalouis]

Hi Ruth, great video (as always). One problem though: If you use a dynamic chart (to show different measures via Measure Selection) and the measures should have different formats (%, Currency and 0,0 e.g.), you can't use format because the format of Mesure selection switches to text. Measure Selection = SWITCH( TRUE(); VALUES('Measure Dimensions'[Measure]) = "Umsatz je Tech & Tag"; [Umsatz je Tech & Tag]; VALUES('Measure Dimensions'[Measure]) = "Utilization"; FORMAT([Utilization];"0,0"); VALUES('Measure Dimensions'[Measure]) = "Einsätze je Tech & Tag"; [# Einsätze je Tech & Tag]; BLANK()) Without format it works, with format the chart doesn't show anything. Maybe you know a solution. This could be interesting for a lot of users, I think. KR Alex

[@CurbalEN]

+TheAalouis Hi Alex! Yes, i have this issue too, but I havent found a solution for it yet... Maybe somebody else here knows? /Ruth

[@MicahandModesta]

Fantastic! Didn't know you could customise. I am wondering, is it at all possible to do the same to give conditional formatting to an Axis that is going off a calculation and not a category? I cannot figure that out! E.g. when a % is picked on a slicer, we see %s going up the y axis and if $ is picked, we see $... up the axis. I've tried using something like the following ($amount and %amount has already been formatted, but still doesn't work either way!) Working, but only as a number (e.g 60k for $ or 0.5 for %): total = switch(true(), test 1 = "$", format(sum($amount)), test1 = "%", format(sum(%amount)), Error, but attempting to force a custom format: total = switch(true(), test 1 = "$", format(sum($amount), "$#,##k"), test1 = "%", format(sum(%amount), "0.0%"

[@CurbalEN]

Your comment got spammed (code is not allowed by youtube). /Ruth

[@ShripalOswal17]

Thank You !! This was very useful :)

[@lisapoppenhouse6663]

Thanks Ruth! By the way have you ever heard of a PBI pro creating a 100 year date table with every possible date format option included and then saving the report as a template and making it available to the public?

[@CurbalEN]

+Dave Poppenhouse Hi Dave, No I haven't, is that something you need to do? In the old Microsoft marketplace there was a service to pull calendars with a lot of regional settings from different countries, but not in the extent you require! /Ruth

[@kasturi2666]

Hi Ruth, Can I use FORMAT to format numbers in scientific notation to convert to a standard format?

[@CurbalEN]

+Kasturi G If you are asking, does it mean that it didn’t work when you tried? I would say yes, but I haven’t tried it myself. /Ruth

[@kasturi2666]

Yes, I tried it but it didn't work. Is there any other way to achieve it?

[@leyshonrr]

Great video, I used it to include B for billions too. My follow up question would be - once the units are formatted into K and M, you now have a character in the data. This is no longer an integer type. Is there a work around here? I'd love to use this newly created column in my charts to show B, M and K.

[@CurbalEN]

Not that I know of... you need to convert it to a a number and then change the data format so it displays K, M or B, but check in the power bi community to see if somebody knows how :) /Ruth

[@leyshonrr]

@@CurbalEN Thanks Ruth!

[@ahmedal-dossary4386]

Very useful as always. Can built-in number formatting from visual section be changed? It will not be practical to change all dax with format function. For example, change million from ‘M’ to ‘MM’ and thousands from ‘K’ to ‘M’.

[@nipunavidanapathirana4968]

Thank you so much for all your videos! This was very useful! However, I am using SWITCH function with FORMAT on a dynamic table. Since this convert numbers to TEXT, I cannot sort numbers in columns. They sort from either 1-9 or 9-1as text. Is there anyway to sort them as a value? I really appreciate and ideas or tricks! Thank you!

[@kartickumar638]

HI Ruth, I am trying to format a number to currency (currcolumn = FORMAT((CalculatenRank[Sales]),"Currency") , but does this convert number to TEXT ? Output of above calculation is not getting aggregated. Thanks for video n all help !!

[@CurbalEN]

I am afraid that is what it does... the other option is changing the data type to currency. /Ruth

[@kartickumar638]

Curbal Is that a expected behavior of format function in the way to convert to currency ? (currcolumn = FORMAT((CalculatenRank[Sales]),"Currency")

[@CurbalEN]

Change the data type instead, it will keep the original setting. /Ruth

[@kartickumar638]

Curbal Thanks alot for quick reply. Even if i change the data , its still remain the text .

[@kartickumar638]

Millions&Thousands = SWITCH(TRUE(), [Custom Numeric]>=1000000, FORMAT([Custom Numeric], "#,##0,,M"), [Custom Numeric]>=1000, FORMAT([Custom Numeric], "#,##0,k"), FORMAT([Custom Numeric], "0")) even this convert the number to text ...

[@manishrathor9209]

Hello i have a question that i have a record in which there is some of negative values and some positive and i create some calculated measure so i got a correct result but some of value is negative and some of positive so i want all the value in positive in measure so which function or how can i got all value are positive in measure so i can show in a visual positive records through a measure.

[@MrAmitkhanduri1]

Hello curbal, I've a question if we've a value 27065347 the how we show the time like 7518:09:07 However I got answer in Excel but the [h]:mm:ss formatting not work in power bi please answer me asap.

[@CurbalEN]

Hi Amit, can you please post in the power bi community? Thanks! /Ruth

[@MrAmitkhanduri1]

sorry curbal i don't have the login credentials for that

[@davemcdonald5170]

Hi Ruth, Is there a way to get hours greater than 24 using format [H]:MM like in excel

[@jagadeeshpinninti]

Is it possible to achieve number in India format like 26,35,76,83,635

[@nileshchouhan3777]

For Date field we don't have enough formatting. I need to format date field like - MMM-yyyy e.g - Jan-2019 Power BI does not support this type of formatting. Is there any other way to achieve it.

[@CurbalEN]

Hi Nilesh, FORMAT can do the same as excel can do, use the same syntax! /Ruth

[@nileshchouhan3777]

Hi @@CurbalEN, I tried it. but it is converted into text format, not in date format. DAX Expression : my_date = format([Date],"MMM-yyyy") output coming : Jan-2019 When I apply to sort on this field. It will sort in alphabetically order. Apr,Aug.... Regards, Nilesh Chouhan

[@craigdormire7261]

Hi, thanks for the video. I learned a couple things. However, I'm having troubles in my use case where PBI/DAX is converting my new column with the formatted numbers to a TEXT field so then I'm unable to aggregate and/or graph. I know what I'm trying to do is a bit more complex, but it the logic works until I add in the FORMAT function. SAMPLE DAXv1 (returns the values correctly formatted, but as a TEXT field): NewColumnV1 = SWITCH(true(), [Attribute]="CategoryValue1",format([MeasureField1],"$#,##0,,M"), [Attribute]=" CategoryValue2",format([MeasureField1],"general number"), format([MeasureField2],"percent")) SAMPLE DAXv2 (returns the values from the desired source field as a NUMBER field, just not formatted correctly): NewColumnV2 = SWITCH(true(), [Attribute]="CategoryValue1",[MeasureField1], [Attribute]=" CategoryValue2",[MeasureField1], [MeasureField2]) Thanks in advance for any ideas/guidance!

[@naucifacio]

Is it possible to give different formats to a single measure depending on a selected value? Edited 5 min. later: Well don't worry i've seen you've had the same problem without finding a solution :(

[@CurbalEN]

Maybe, something like this? m.kzbin.info/www/bejne/bmm0hJlom6dkiMk /Ruth

[@perezmarcone]

Format satan. :D

[@CurbalEN]

Did I say that? 😂😂 /Ruth

[@perezmarcone]

@@CurbalEN when you are using format. si jajajjaa.