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!
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)
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
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.
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)
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.
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!
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 🙂
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
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 ?
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!
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.
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 ?
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.)
@@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.
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?
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!
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?
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.
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 😭
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!
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.
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?
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.
@@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.
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.
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
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
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 ?
@@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?
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?
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
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.
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.
