DAX Fridays! #32: FORMAT
Vložit
- čas přidán 30. 03. 2017
- In today's video we will go through FORMAT function in DAX. We will learn how to use pre-formated functions and custom functions to format numbers and dates using DAX.
Link to download the file: curbal.com/blog/glossary/FORM...
EXCEL SURVEY: 1drv.ms/xs/s!Ar8CDNp8cGTcgjaH...
Keynotes:
01:00 Microsoft documentation on FORMAT DAX function
02:23 Pre-defined number formatting: "General Number"
03:30 Pre-defined number formatting: "Currency"
04:38 Pre-defined number formatting: "Fixed"
05:10 Pre-defined number formatting: "Standard"
06:00 Pre-defined number formatting: "Percent"
06:23 Pre-defined number formatting: "Scientific"
07:31 Format numbers without decimals
08:05 Format numbers with four decimals
08:45 Format numbers to millions and thousands
10:18 Format numbers with Leading zeros
10:51 Format numbers with one decimal
11:09 Format numbers with percent symbol
11:25 Format numbers with Scientific symbol
11:53 Pre-defined date formatting: "General Date"
12:10 Pre-defined date formatting: "Long Date"
12:40 Pre-defined date formatting: "Short Date"
12:49 Pre-defined date formatting: "Long Time"
12:50 Pre-defined date formatting: "Medium Time"
12:55 Pre-defined date formatting: "Short Time"
13:17 Format dates to return the Day number
13:22 Format dates to return Month number
13:30 Format dates to get dd-mm-yyyy format
PREVIOUS VIDEO: • DAX Fridays! #31: Roun...
NEXT VIDEO:
Looking for a download file? Go to our Download Center: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
#daxfridays #curbal #SUBSCRIBE
► Linkedin: goo.gl/3VW6Ky
These videos are little gems! Thanks Ruth
Your videos are always there to save my day!
Yey 🎉🎉
/Ruth
Thank you so much, you finish well my weekend 😀
Excellent 👌
You are making our life easy with the KT. Thanks!
My pleasure!
/Ruth
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...
+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
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 !!
+Victor Friesen Thanks Viktor! Have a great weekend :)
/Ruth
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.
Perfect! Glad ir was useful!
Muchisimas gracias, como cada video donde se aprende tanto en todo lo que enseñas . Te agradezco mucho.
Muchas gracias a ti por el feedback y buen fin de semana!
/Ruth
@@CurbalEN buen finde para ti también! 🦋
From Argentina congratulations excellent videos
+Alejandro G. Messina Hola Alejandro,
Muchas gracias y buen fin de semana! :)
/Ruth
Very interesting and helpful video ! Thanks Ruth ! Have a great weekend 👍🏻🎉🌞🌞🌞🌞🌞
+SmartBimson Have a great weekend too Andy 🏝!
Will be more 🏌🏼with a lot of 🌞 ... spring is coming !
+SmartBimson Not here...yet... so enjoy it for me! 😄
/Ruth
Thank You !! This was very useful :)
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
+TheAalouis Hi Alex!
Yes, i have this issue too, but I havent found a solution for it yet...
Maybe somebody else here knows?
/Ruth
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!
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?
+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
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%"
Your comment got spammed (code is not allowed by youtube).
/Ruth
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’.
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.
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
@@CurbalEN Thanks Ruth!
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.
Hi Ruth, Is there a way to get hours greater than 24 using format [H]:MM like in excel
Hi Ruth,
Can I use FORMAT to format numbers in scientific notation to convert to a standard format?
+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
Yes, I tried it but it didn't work. Is there any other way to achieve it?
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!
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 !!
I am afraid that is what it does... the other option is changing the data type to currency.
/Ruth
Curbal Is that a expected behavior of format function in the way to convert to currency ?
(currcolumn = FORMAT((CalculatenRank[Sales]),"Currency")
Change the data type instead, it will keep the original setting.
/Ruth
Curbal Thanks alot for quick reply.
Even if i change the data , its still remain the text .
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 ...
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.
Hi Nilesh, FORMAT can do the same as excel can do, use the same syntax!
/Ruth
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
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.
Hi Amit, can you please post in the power bi community? Thanks!
/Ruth
sorry curbal i don't have the login credentials for that
Is it possible to achieve number in India format like 26,35,76,83,635
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 :(
Maybe, something like this?
m.czcams.com/video/88QSf2fB1Rg/video.html
/Ruth
Format satan. :D
Did I say that? 😂😂
/Ruth
@@CurbalEN when you are using format. si jajajjaa.