DAX REVOLUTION!? Visual Calculations in Power BI
ฝัง
- เผยแพร่เมื่อ 5 มิ.ย. 2024
- Join my Power BI Design training here: my.datatraining.io/pages/powe...
In this video I explore visual level calculations, a new way to write DAX that is easier.
Enjoy this video and subscribe to always stay updated on my favorite Power BI tricks :)
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
00:00 Intro
00:50 Templates for visual level calculations
07:37 Running calculations
08:54 Rolling (window) calculations
13:14 Using parameters with visual level calculations
17:37 Comparison to table calculations in Tableau
19:03 Level of detail calculations
22:53 Reusing visual level calculations for conditional formatting and reference lines
24:30 Using visual level calculations in custom visuals
25:54 What native visuals do not support visual level calculations
26:47 Using calculation groups with visual level calculations
28:22 Direct query mode and visual level calculations
28:53 Speed: are visual level calculations quicker or slower?
31:41 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on TH-cam: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX - วิทยาศาสตร์และเทคโนโลยี
![[UNCUT]"เศรษฐา" โดนบีบให้ลาออก เผยดีลลับ "ทักษิณ" ต้องต่อโปร ไม่งั้นเจอจุดจบ! I คนดังนั่งเคลียร์](http://i.ytimg.com/vi/XlH3k81dxmE/mqdefault.jpg)
Hello I just want to say Thank you! My company restructured and I ended up in an analytics role that I know little about. I’ve used your videos to guide me and you have pulled me out of more jams than I can mention. So thank you for making these videos and teaching us all.
that's awesome 😎👊 happy I could help a bit on your journey
The most complete video about Visual Calculations! I really enjoyed.
Thanks a lot Bas!
Thanks Bas for this. You nicely put the new function into context. Thanks for taking time to prepare this session for us, quite useful.
Thank you for checking out our visual!
And yes, the on-visual PBI calculations do work in our visual, but honestly, we strive to make things easier for our users. So, the variances between values and other measures placed in the PY, PL, or FC (values - actuals, PY - Previous Year, PL - Plan, FC - Forecast) are all automatically calculated in the visual without the need for any DAX, calculations, etc. Just your basic measures.
And just an FYI to all who are watching, we have had our own version of on-visual calculations in our visuals for quite some time and have been receiving great reviews. We would be glad if you would check them out.
Fun fact (I'm sure Baz knows this but for everyone else) if you double click the sales pill at 18:22 you can adjust the control using a calculation interface. The nice Tableau UI is just adjusting these values for you but sometimes you get more control by tweaking the calculation yourself and you can also use this in tandem with other calculations for context. Great to see the comparison to Tableau.
power bi has some cathing up to do here 😁
Hi Bas, Please keep up the good work. Your research saves the rest of us lots of hours in trial and error!
thank you for this very complete overview, looks like a wishlist directly for Microsoft 😆 I've been using power bi a lot in the last years but I also did a year with Tableau. I was indeed missing some "calculation in one-click" from Tableau that are not very powerful but are just cool and time-saving. So good addition in my opinion. Still I feel like the report may be harder to understand (for creators) now that we have calculated column + dax measure + calculation (+ filter + renamed fields in visual) so I don't think I will use in reports with many pages in which I re-use my measures a lot.
Wow, thank you very much! I love you how detailed this walkthrough is. And I really appreciate the comparison with Tableau - I switched from it some time ago and I really missed these options in Power BI. I am very excited about this feature and the upcoming updates as well!
Yea table calcs in tableau r nice! But guess powerbi is gonna catch up quickly now… maybe …
I just started learning power Bi for a month now and I was able to build my own dashboard. I am still learning DAX formula and this addition certainly will help me
I think it definitely could 😁 good timing for starting with power bi
For sure. I have minor problems with slicers and im trying to figure it out myself. 😆
Your videos are awesome and do help me to learn something new and try out-of-the-box things.
Thanks for evaluating the performance aspect of it. Rarely ever discussed when using new features. Great work!
yea needs a bit more testing still though :) but was curious myself if the speed would be drastically different
Official document says it may be faster, but apparently not (yet?)
Nice no-brainer video! You made it easy for me! Thank you
Thanks for amazing content .. I hace a question, how i have the Data, Visualizations, FIlters, etc. options applied to the right?
The Switch to Tableau and compare the 2 BI Tools within its Features is Great! Could be a be another big advantage to watch your videos. Keep going! Thanks for this one 🎉
😎👊
Thanks for sharing your creative insights on this new function. Much appreciated!
😊 thx for watching
Thank you for your greate summary about visual level calculation
Great video! Showing all “pros” and CONS!
thx! 😊
24:30 Thanks for this deep dive. Even considering you test things that the documentation already says it's NOT possible to do yet under the long Considerations and limitations list
Another (current) limitation: it doesn't work with small multiples...
Thank you, Bas! Great walkthrough! 👍 I Love the simplicity of VisCal. It will be awesome for some use cases where you DON'T want to add another measure to your models!
this will make my life easy hopefully, Thanks Bas. ❤
You are the best!! Thank you so much for sharing your knowledge!!! 🥲👏👏
Great addition for that off the shelf calculation, especially for beginners...
Now matching tableau
almost matching ;p not quite there yet
@@HowtoPowerBI exactly
Very detailed evaluation of current possibilities of VC, thanks. One remark, when using performance analzyer most relevant results are when you create blank page, restart PBI Desktop, turn on performance analzyer and then go to page you want to evaluate - this will clear the app cache and give more accurate results.
thx for the tip, it was just meant as a quick insight into the speed. Got to the same results clearing cash / also using dax studio. Some more testing to do though
Very comprehensive and detailed as usual, many thanks.
THANKS! :D
I love your videos they help so much thank you! Is there any chance of taking a step into some sort of call centre analytics dashboard guidance? There's such a lack of this across the web it seems!
This great! Really easy to add in a 5 day or 20 day moving average with daily sales.
Hi, do you know how to show the grand total in the "versus previous" when you change the first value with blank?
Hi Bas, I am not sure if it's been raised in the comments already, but for the Running average VC (10:14 in the video) - in Q3 you have an MA for Q3 and Q2 (because you chose a window of 2, (67.95+71.49)/2=69.72). The Q1 is not included :)
Really neat and definitely something that's going to help users! Great video as always🔥
Going to be a pain maintaining reports heavily using these with calculations existing in the visual though. Would prefer them organised into nice measure tables 😅
Guess you will be able to add them to the model later
Thanks Bas for such a thorough review. It’s reassuring to know that visual calcs (VC) are more expensive than a structured DAX approach. Added to the inability to include in documentation tools and the other limitations of them, I can see exactly where I can use them and advise on their use.
To start, I will be using VCs when in discussions with the client, reviewing their data in real time. For those colleagues wanting to carry out local analysis but without the knowledge of advanced DAX, I will be strongly advocating VCs, which have the added advantage of saving me having to implement what would now be simple changes - moving a little closer to self service analytics?
VCs are fantastic for me moving from Tableau to PBI - very timely too as I’m currently converting my current models to PBI and have had to first learn the platform to know the best approach to start with!
For someone with DAX knowledge, what advantage if any would there be to using VC over measures? I had thought the performance over the aggregated matrix might be a good reason, however it seems the opposite is true.
Oh man. My organization is stuck on Jul 2022 version of PowerBI. I've spent a long time doing pivoting/unpivoting columns etc in M Query to come up with a way of simply showing variance to the last period. It's good to know we'll get this as a feature some time in the future though!
Great in-depth presentation. Thank you. But didn't they also promise to ease calculation of totals when you have a little more complicated calculation in a measure?
Thanks a lot!
These are interesting- but I don’t think I’ll be an early adopter yet. Really helpful video tho.. loved as you went thru trying things I would have tried that don’t work yet… saves me from frustration 😀
Great video Bas. Thanks.
Since it is not possible to reuse these measurements, it seems completely useless to me for slightly larger projects. Maintenance would be hell.
Good for those just starting out.
OMG, seriously. And no conditional formatting, cannot use parameters to control the visual. Ok fine, this is the first phase…but really not fine. These concepts are not new. In QlikView, I was able to do this 10 years ago and also was able to have reusable measures too.
These vis calcs are like calculated fields on excel pivots that can’t be conditionally formatted. Brilliant.
I think this is my favorite video from Bas as shows his disappointment (in a very polite manner) with PBIs poor execution of what could be a nice feature. Even if they do improve the functionality, I’m still annoyed with their policy of releasing garbage and waiting for users to demand changes.
Thanks Bas for all of your great videos. You have a great style and enthusiasm - I’ve learned a ton.
Awesome content, i have been struggling with runningsum issues but with this video i am confident that i can solve it
Yes the visual levels calcs r much easier
Great analysis 🇧🇷
thank you!
Great informative video as usual Bas. Thanks a lot!
😊 thanks 🙏
First thing I notice is that it can remove some resource limitations and I already have a use case where it comes in handy.
However the use case will (even though I hate it) require that users can extract the list to excel. This is not possible with a Visual Level Calculation from what I see.
indeed not possible (yet?) ☹️
No field parameters is a huge limitation of this feature :(
Thanks for sharing
i was waiting for your video, thank you BAS
And I was waiting for you to watch it 😄
Hi Bas, do you know how to fix the problem, that visuals with these DAX customizations are not able to be shown in published reports via weblink?
18:47 Microsoft hasn't been able to insert/offer Excel Pivot Tables UX in PBI something that Tableu is offering pretty much since day 1.
Thanks Bas for this thourough review. Extremely helpful
Glad it was helpful!
Excellent again Bas @HowtoPowerBI. But I think there is a way to format number of the visual calculation.
Unfortunately I cannot copy past a screen here. But for instance when in your example you make your Running Sum.
Go to the Formatting option of your visual ( so the visual not formatting of the calculation).
There go to 'Specific Column', choose the series 'Running Sum', then from the Display Units choose millions and set the decimals to 2.
(only thing is you will get an M, but you could set the original the same)
😊 thx .. that is just display unit , what if u want percentages?
@@HowtoPowerBI true, it aint much but more than nothing :). There is no percentage notation. But for people like me who use mostly the basic/standard formatting there is no need to compare your million formatted number, visually, with an 8 or 9 digit number with also 2 decimals. It is far from perfect and hopefully they will add things soon.
Is anyone else getting a constant restart issue on this video? Around 5-6 minutes in, it keeps flicking to ads and then starting over for me. Not giving up though, this is gold!
How about a chart that shows 12 months. I'm interested in learning how to calculate a person's stay or length of stay and I like that rolling calculate if I did something for a 12 month and then at the end a 12mth.
Appreciated Boy :)
These are really great for people who are newer to DAX, thanks Bas!
agree, I'm curious to c how this is gonna develop over the next few months
Infact it is actually bad for those people who are new to DAX. And this will be realised at a very later stage.
These calculations are either for a casual Power BI user or for someone who wants these calculations on ad-hoc basis, and is not really designing a Power BI semantic data model.
You ARE A GENIOUS!!!
Naaaah.. just showing where to click 😉
Amazing Video Bass.. Keep it up
😃☺️ thx
Hi Bas, I have a question:
I have a database with a current stock overview of a warehouse.
This is refreshed daily to see the current stock.
Do you know how to create a YTD table/graph with daily stock movements in PowerBI?
I look forward to your answer,
Groetjes uit Nederland
this hopefully gives you a starting point -> www.sqlbi.com/articles/inventory-in-power-pivot-and-dax-snapshot-vs-dynamic-calculation/ groetjes :D
DAX is cumbersome, regardless of whether you are a seasoned pro or not and it always has been and the introduction of Visual Calc removes a basic pain point many have when simply trying to do calculations that apply to most visuals. I'm glad Microsoft has supported this feature but it's only shone a light on how there still remains too much friction in using DAX for simple things.
Thanks for sharing such a descriptive video on this. Just asking, do you happen to have a similar channel on Tableau? If yes, can you share the link to that channel as well?
HowToTableau, hopefully some time this year ( just too few hours in a day 😅 )
@@HowtoPowerBI . Eagerly waiting for the same wherein we can learn Tableau step by step.. 😃
Great content 😁❤
🙏 thanks
Hi if anyone know how to hide Slicers in multiple pages while exporting report?
I want to calculate the number of empty cells per column,
Is it possible to make a new table with two columns: Name column and Number empty cell.
I would like to know the name of the cells and the number of empty cells for each column of my main table.
knowing that my main table has 79 columns.
Via Dax language to make my work easier
How do I do this?
Thanks a lot
Hi,
I am using metric visual in Power BI, in column field, I used year month column, in rows Product group and plant name.
Now my condition is max value in red color and min value in green color, but max & min value are decided at row level.
For suppose, 'abc' plant have 22 max value and 3.5 min value, but in 'xyz' plant name 38 max and 12 min.
So, for 'abc' plant name 22 in red and 3.5 in green, same with 'xyz' plant name 38 in red and 12 in green.
Can you please help me to find exact solution?
maybe this helps th-cam.com/video/hmk6PxDtbNs/w-d-xo.htmlsi=MexyLLYD2hZkanwB
Thank you for learning me Power BI from your channal. I'm encountering an issue and need your support. Whenever I utilize new visual calculations and publish my dashboard to the web, any tables or charts containing these new calculations fail to work properly and give me this erorr "Couldn't load the data for this visual". Can you assist me with this?
is not supported yet .. you'll need to wait till one of the next updates
Thank you for your fast response can you till me how i can use moving average in Dax @@HowtoPowerBI
What is the equivalent of PREVIOUS in DAX?
Helpful video, thank you! Long live to Visual Level Calculations!
🎉🎉🎉
One more restriction: if you add visual calcs to a table, it will disable export. I assume this is temporary as the feature develops, but it's worth noting.
Thanks! 🙏 hadnt checked that yet 😃
Hello thanks you for your videos , please could you make a comparison video between power bi and Digdash, find out who is the most impactful😊
dont have much experience with DigDash sry
I was hoping that storing a numerical parameter in a variable may fix the issue with the moving average, but nope.
Was worth trying 😄
Did anyone else here encounter an issue where the application freezes after clicking the New Calculation button then crashes? It says out of memory then points me to the page about webview2. Thing is I only have 2 tables and a singular graph so I doubt I was using that much memory. No custom visuals either.
didn't hear about that issue yet sry
unable to download file even followed instruction as shown!
Please check it again works now!
Here we go.BAS to crack the code 😀 !!!
haha let's see what doors this is gonna open 😁😅
It's great. Coming from excel to pbi will be much easier, but what about all these hours wasted on learning DAX in the last few years 😂
:p maybe microsoft is gonna compensate you for that 😅
Need to test if this works on local files or you have to be in the semantic model.
This feels like a video for Microsoft so they can see what to add
Lets c if they listen lol
25:54
I don’t understand the argument that with visual level calculation it’ll be easier for a newbie. The visual level calculation is based on a visual field. In other words, the visual level calculation in most cases is based on a measure. To create a measure a user must know DAX in the first place. The knowledge of DAX is compulsory. So, for me the visual level calculation is a chance to speed up the report. The only expectation I had in this regard is that visual level calculation might improve the speed of running total calculation. Indeed, I see a boost of ~30% for calculation of running total via visual level calculation. Nevertheless, I am quite disappointed by such a small improvement, so I have doubts whether it’s worth to use visual level calculations at all.
not really. lets take the example from the video. To get a moving average you just drag in the sales column, and you can use the function movingaverage. Traditional DAX be a few lines that you can only write with a good understanding of dax. Same for running total.. it is way easier to get the calculation you want. Performance wise i only found visual level calcs to be slower, where did you observe it to be faster?
@@HowtoPowerBI I haven’t checked all the cases. I saw the results in your video, and I find them quite satisfying. The only case that I checked is running total. I used one of my production reports to perform a test. The calculation logic of a production running total measure is not straightforward, so the measure is a bit slow. My expectations were that visual level calculation might be faster as they promised less iterations involved. Actually, a look at a DAX code copied from Performance Analyzer leaves no doubt that visual level calculation of running total has to be faster than my measure in production. As I said the measure in production is not blazing fast. The performance got boosted, but not as much as I’d like to.
Interesting for me the visual level calculations have been slower and all test cases i ran
Ok, no fields parameters and conditional formatting is enough for me to not use them :D
not yet .. but who knows what's coming
Thanks for comparing it with Tableau. IMO, Power Bi is going to be new excel. Microsoft is going to sell it aggressively.
it is gonna grow for sure .. not replace excel though
I don’t like it! It is certainly targeting “self-serve BI” users who never wanted to really learn DAX, and now probably never will.
But, after spending years learning the language, I won’t touch it. Easy is not a synonym for optimal.
Just an example: what if you need to compare the number with the one before the previous? Quick measures don’t solve this.
Writing DAX is an art, and I learned much from you. Thank you!
previous ( [total sales] , 2)
hmmmm, hehehe. Anyway, my bad example does not harm the argument. Learning DAX the hard way somehow changed the way I think, and I'm glad it did.
This is nothing but a trap, where if people become useto of writting such calculations, will forget how DAX actually works, and whenever they need a little bit extra and they will look into DAX as per the semantic modeling percepective, they will easily get confused to understand the actual evaluation context and behaviour of expressions.
So people who are willing to start their career as Power BI professional, they should keep in mind that, these expressions has nothing to do with your actual model and are valid only for that visual itself. And thats why it is called visual calculation.
If CALCULATE is the queen of all DAX measures, then now we've just got a couple of pawns right away. I liked to play chess/ write DAX the old way, but after all, any pawn can take the queen if it is in the right place and the right time. We'll see... ♟🤷♂
nice analogy , indeed let's c how these viz calcs develop further
25:54