Thanks for the great video! I've watched a few other videos talking about dynamic format strings but this is the only one that tackles possible impact on performance and reusing format strings by calling on the format string of another measure. One question, is it possible to use dynamic format string to show percentages without the % sign? For example, instead of showing 45.6%, I want it to show only 45.6
I've notice that using dynamic format string disable the use of tabular editor (since the first release and yet with mast update). Has this happened to you too?
It would be great if there was a setting that you could toggle stating that the format string measure will be the same for all rows and therefore calculate one time and then apply to all rows in the column for that measure. For example, I have a disconnected table and slicer where they pick what currency they want to show / convert transaction values to. I know what whatever currency I select, I then want that currency format string so it is dynamic. So I don't need the query to be evaluated each row... just once. I don't know if this is possible but I don't use the format string feature because it is a "nice to have feature" and my users care more about every second of wait time. Love the content. Thanks for sharing.
Great video! I just tried to use this feature hoping that it would allow me to properly chart minutes and seconds converted from duration. What the conversion would look like (1.5 = “01:30”) using “nn:ss” . However it seems that it still couldn’t chart this even though I can see the correct result in a matrix. Is there something I am missing or is still not possible?
@@SQLBI Thanks! that is what I ended up doing as well as a matrix with correct format. Maybe it'll be possible to chart one day. Thanks for all of your resources, they are invaluable!
No, the colors inside the strings cannot be changed with the regular custom visuals. You should look for a custom visual that support rich text (we don't know whether there is one).
It's complex and it depends on many factors - the Optimizing DAX video course spends so many hours to analyze that! www.sqlbi.com/p/optimizing-dax-video-course/
Hi I am trying to work around the lack of a duration data type. I store my durations as decimal numbers. The integer portion of the duration is the number of days and the decimal portion is fractions of days. I want to format a total measure as d hh:mm:ss. DAX does not seem to want to apply format strings like hh:mm:ss to decimal numbers. Any suggestions on how to accomplish this?
@@SQLBI Hi thanks for the reply. I have tried that but the problem is that returns a text value which is OK for tables but you can't use the measure on the y axis of a chart. I agree that MS needs to do something about durations. Options are: make a proper duration data type, allow dynamic formatting to return a formatted value (as opposed to a format string) -- then you could use FORMAT as you suggest or simply allow date time format strings to be applied to decimal values. It's very frustrating that there isn't a natural way of working with durations as they arise in all sorts of contexts such as payroll, project or work order tracking. If any one has other ideas I would love to hear them.
@@SQLBI Sorry my bad. I have tried both. If you convert to a DATETIME value you have the same problem. The measure cannot be used on the Y axis. Here is a simple and very real use case. My data is Date, Person, Project, Effort where Effort is provided in hh:mm:ss. Produce a stacked bar chart with Date on the x axis and total effort on the y axis and project in the legend. The user should be able to drill up and down the date hierarchy to effort by day, week, month, year. The y access should display the effort in d hhh:mm:ss as should the tooltips.
The level of depth in your videos just keeps astonishing me. Seriously, you are the best that ever happened to PBI users. ❤
Very neat, thank you so much! :)
hah i showed them the multi-comma thing!
Thanks for the great video! I've watched a few other videos talking about dynamic format strings but this is the only one that tackles possible impact on performance and reusing format strings by calling on the format string of another measure.
One question, is it possible to use dynamic format string to show percentages without the % sign? For example, instead of showing 45.6%, I want it to show only 45.6
this is good but problem with this approach is that if you are bring 4-5 formatted KPI's the visual breaks with the error the resources exceeded
How to use this in a live connection dataset?
I've notice that using dynamic format string disable the use of tabular editor (since the first release and yet with mast update). Has this happened to you too?
It would be great if there was a setting that you could toggle stating that the format string measure will be the same for all rows and therefore calculate one time and then apply to all rows in the column for that measure. For example, I have a disconnected table and slicer where they pick what currency they want to show / convert transaction values to. I know what whatever currency I select, I then want that currency format string so it is dynamic. So I don't need the query to be evaluated each row... just once. I don't know if this is possible but I don't use the format string feature because it is a "nice to have feature" and my users care more about every second of wait time. Love the content. Thanks for sharing.
That's a good point for performance. You should post it on ideas.fabric.microsoft.com/
Is it possible to do date formatting with these dynamic format strings?
Great video! I just tried to use this feature hoping that it would allow me to properly chart minutes and seconds converted from duration. What the conversion would look like (1.5 = “01:30”) using “nn:ss” . However it seems that it still couldn’t chart this even though I can see the correct result in a matrix. Is there something I am missing or is still not possible?
You should consider using the measure with the formatted time in a tooltip and the numeric value in the chart.
@@SQLBI That's a good idea. I hadn't thought of that. TY
@@SQLBI Thanks! that is what I ended up doing as well as a matrix with correct format. Maybe it'll be possible to chart one day. Thanks for all of your resources, they are invaluable!
Update, it is now possible to accomplish this with the new custom data labels in this months release!
I want to be able to change the colour of a negative value inside a text string. Is this possible with this new feature ?
No, the colors inside the strings cannot be changed with the regular custom visuals. You should look for a custom visual that support rich text (we don't know whether there is one).
Nice explanation, is there way to know which dax function uses storage engine or formula engine
It's complex and it depends on many factors - the Optimizing DAX video course spends so many hours to analyze that! www.sqlbi.com/p/optimizing-dax-video-course/
Is it more efficient processing when numbers are noted using exponents (1E3) instead of writing them out fully (1 000), or is it just for legibility ?
Just for makes it easier to read/write them.
Hi I am trying to work around the lack of a duration data type. I store my durations as decimal numbers. The integer portion of the duration is the number of days and the decimal portion is fractions of days. I want to format a total measure as d hh:mm:ss. DAX does not seem to want to apply format strings like hh:mm:ss to decimal numbers. Any suggestions on how to accomplish this?
It seems there is a way: you should convert the result into a DATETIME in the measure - write
CONVERT ( ... , DATETIME )
Then the format should work!
@@SQLBI Hi thanks for the reply. I have tried that but the problem is that returns a text value which is OK for tables but you can't use the measure on the y axis of a chart. I agree that MS needs to do something about durations. Options are: make a proper duration data type, allow dynamic formatting to return a formatted value (as opposed to a format string) -- then you could use FORMAT as you suggest or simply allow date time format strings to be applied to decimal values. It's very frustrating that there isn't a natural way of working with durations as they arise in all sorts of contexts such as payroll, project or work order tracking. If any one has other ideas I would love to hear them.
We suggested CONVERT, not FORMAT! Did you try it?
@@SQLBI Sorry my bad. I have tried both. If you convert to a DATETIME value you have the same problem. The measure cannot be used on the Y axis. Here is a simple and very real use case. My data is Date, Person, Project, Effort where Effort is provided in hh:mm:ss. Produce a stacked bar chart with Date on the x axis and total effort on the y axis and project in the legend. The user should be able to drill up and down the date hierarchy to effort by day, week, month, year. The y access should display the effort in d hhh:mm:ss as should the tooltips.
how to format negative number into (#,.0K), (#,.0M) etc
So the most important thing is last. You can’t use this if you use SSAS