My beginner's DAX mistakes, ......what are yours? 😈
Vložit
- čas přidán 1. 07. 2024
- In todays video, I try to go through my DAX sins (things that you better not do if you want to keep your model "alive").
The weather changes for the worst, so I will continue in another video.
But, what are your DAX sins? :)
Here you can download all the pbix files: 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
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/product/sponsor-me
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE
Coming from an Excel Background I was naturally drawn to Calculated Columns over measures. However two big tips changed my view on this and pushed me to embrace best practices.
1) Use Calculated Columns only for things you need to slice, filter or group by. Things you would put in the rows, filters, slicers or columns in a pivot table. Use measures for everything else, specifically things that you would put in the values area of a pivot table. The reasons for this are beyond the scope of this post but are easily found on google ('Calculated Columns vs measures DAX')
2) Think of the 'iterator' (X) functions as building a 'virtual' calculated column that you don't see materialize before your eyes but is called X. An example. Take 'SUMX ( Internet Sales, [Internet Sales]Net Price * [Internet Sales]Quantity )'. Now let's break this down. The first thing you tell DAX is where you want this 'virtual CC' called X to be built. This is in the table you provided in the first argument, in this case 'Internet Sales'. Then you define what calculation you want to use for the 'calculated column'. This is actually the calculation you would type in if you were creating this as an actual calculated column in the table internet sales. The calculation in this case is the column 'Net Price' multiplied by 'Quantity', and this for each row. The last part is what you want to do with the result of all these row calculations. You provided the formula SUMX so you want to sum them! So basically you: SUM column X in table Internet Sales containing the calculation 'Net Price * Quantity'. This is equivalent to making a calculated column in the Internet Sales table with the formula 'Net Price * Quantity' (and then setting the aggregation to sum or SUMming it). Thinking like this when you come from an Excel backgorund makes things like evaluation context, context transition and complex measures much easier to visualize in your mind.
Full disclosure: this method of thinking about measures was taken from the excellent article 'MEDIANX Returns Blank in DAX' from the blog Excelerator BI. Which is well worth the read and goes in much deeper detail. Google it.
I learn so much about DAX from your channel. Thank you so Much😊
Glad you enjoy it!
Ruth - Happy Friday! Wow-what a beautiful location. I was really enjoying your video. I'm sad it ended due to the weather. Please do another outside like this. So cool. I love you and I love all your videos. I'm making all these mistakes and I laugh because I just started to learn Dax. It's so true ~ Dax is a simple language but it's not easy and neither is reading The Definitive Guide to Dax. This is why watching your video helps so much to make it understandable and fun. ❤😊
Thanks Joanne ❤!
I got really windy so I knew you wouldn't hear anything.
I will do more, they are fun for me to do too :)
Enjoy your weekend!
Amazing location and weather. Thanks Ruth I also did similar mistakes. I have learned a lot Dax techniques from you. Many thanks
It is raining now!! Crazy weather :)
Amazing location and weather. Thanks Ruth .Learned a lot Dax techniques from you. Many thanks! Have a great weekend ahead !! I still make mistakes!!
Me too and happy friday!!
@@CurbalEN Enjoy :)
Ruth I got similar experience like youts. Started to explore the dax world gudautely and get to know abt the modeling measure and able to learn a bit everyday make my work meaningful plus trying to transforming all the working reports into BI make my life easier just a few clicks to make my boss happy
Yes, it sounds like we walk similar paths!
Happy Friday :)
Thanks for sharing Ruth. Had similar early mistakes. Didn’t we all especially when we look back & remember DAX was relatively new with Power Pivot & later Power BI. I’d add, once I moved to Power BI, the frustrations in locating my measures because they were scattered across different tables! 😂 One of my best lessons learnt was having all my measures in one table / group for measures only. Thanks Sam Mckay 😀
I dont think that having all measures in one table is a good practice. I have a few videos on it.
@@CurbalEN I will watch those videos - another learning from me to always have an open mind. We see if it convinces me otherwise knowing how I came to have my measures in a separate table. Not necessarily in one table, but more importantly not in my data or dimension tables. Of course more than happy to maybe learn something I didn't know with your video and we see. After all with Power BI / DAX / Power Query one learns everyday! :-)
I explain my reasons why here:
m.czcams.com/video/zrqKthwDMok/video.html
/Ruth
Hi Ruth,
Hope ur doing good 🙂
Am new to Powerbi, your videos are great source to learn , thx lot for all ur inputs ...
Have one question
I need substract two dates
One date will come from slicer (user selection) another date is from table
So I need a new column to substract these two dates
I have tried selected value dax but am not getting correct results
Could you please help me out
It's good to know that i wasn't the only one who did all my modelling with calculated columns!
My biggest sin was avoiding relationships and simply merging table together PURELY because it's what made sense.
I did that too 😂😂 , seems like a common trap for excel users!
Looks like very relaxing place your in!
It is, specially this time of the year :)
Using FILTER function inside CALCULATE (which is redundant for single column filter), was the silliest mistake I made so far in DAX measures! 😅
I do that still 😂😂, old habits die hard!!
@@CurbalEN we are on the same page then 😄. Good to see a video where you are sharing common mistakes in DAX! After all, mistakes teaches us some of the best lessons. Cheers Ruth 🥂 Happy Friday! 🔥
Mr mistake is my best teacher 😂
We all have been there. Thanks for sharing!
Thanks for watching!
OMG...super location and beautiful environment ruth
It is 😍😁
Beautiful place Ruth ❤️
It is! 😍
Currently I am relying heavily on calculated measures as well. Although I would want to move most of it to measures. My model has died a couple of times already but I am still powering through. I need to understand what difference will the model make when it comes to using calculated columns vs. measures.
Huge difference, but take it one step at a time, soon enough you will be creating measures in your sleep!
Now I know what is wrong with my model. I use merge for all my BI model and drag and drop to report which kind of easy but performance wise kills everything. Looking back to all my works, I am having hard time to scrap it and build a new one. Probably, I will keep it live for the time being and will not do it again for the new reports. :) :)
Sounds like a good deal 👌
As you see, we have all done that!
Happy Friday, Ruth. Good to see you were walking outside of house. My beginner's PBI mistakes were starting from buiding wrong relationships. That took me a while to learn data structure again. About the DAX, the hard part for me is to use summarize function building virtual tables from different tables and adding filters. :P - Danny
Yes, that was a pain too....I remember my models with bi-directional relationships everywhere 😱😱
@@CurbalEN 100% :P
😂😂
Happy Friday Ruth! I used to do the same... calculated columns, instead of measures. DAX Is best learned by real life examples. Or by the The Definitive Guide to DAX. It can cost you all your well-being, confidence and an existential crisis... but... to answer your question... Biggest obstacle is to get rid of the A1 style references... i needed some time about it. Have you considered summarizing all the DAX Fridays into a course and post it on udemy let's say? Take care and stay safe!
😂😂 I guess all of us that come from the excel world have very similar experiences!
Regarding a dax course, it has been in my to do lisy for the longest time and I still haven't done it, so...
In addition to all of the sins that you mentioned, I have also made mistakes of WORRYING too much about making DAX sins then not completing the projects on time. Had I just gone ahead and opt to using simple calculated columns instead of insisting on complex DAX measures, some reports would have just worked perfectly... BEFORE the deadlines. But then again, these earlier struggles also contributed to my learning, and probably made my productivity and the performance of the later reports better in the end, so this, perhaps, was not the biggest sins... Hope you made it back safely in the adverse weather!
That is a good one: when are things good enough?
With DAX is a easier as if your model is alive an kicking you are doing good 😂😂
@@CurbalEN Right? I often find some cringe-inducing methods (DAX or M) from the published reports from my earlier days, but they are still working perfectly and highly valued in my organization. I guess the moral is "the correct method is the one that works."
My mantra exactly! Couldn't have said it better, thanks!
I have done the mistake of adding calculated columns which can be easily manage through measures. At start I didn't even know about measures 😂😂😂. But now it's getting better and better 😜.
Your model died too? 😂😂
Really nice place ! What place is this ?
Sweden :)
In excel we are able to show time in hhmmss format on x and y axis, but I am not able to find this in Power BI.
Is this even possible.
Use the function FORMAT
Pretty relatable Ruth :)
Crazy how we all make the same mistakes!
@@CurbalEN Ruth, I have been self learning/working on PBI for almost an year and half and have been doing same mistake creating multiple calculated columns every time. Recently I am working on a project which has a large data set and multiple fact tables and when I created multiple columns, I faced major performance issue.... I am using Power query to clean and transform data now... Hope I am on the right track... what do you say?
Difficult to say without knowing the details, but if you are getting better performance , then you are in yhe right track!
Happy Friday!
wind noise :( but ya I made similar mistake of too many calculated columns too :P
Yes, it gor worst while filming😱
"Neither do I condemn thee: go, and sin no more."
Will try ;)
calculated columns are easier when you want to pass your work to somebody else. imagine everything is in measures, fiuh, that guy will probabily pass out before finishing 5 lines of measures. So I think a combination of them would be good. not too many of calculated columns but not too little too.
😂😂 As long as you don't kill your model, everything is fair play imo ;)
Document your measures with // when transferring ownership.
very windy..
cannot hear the audio clearly .. :(
Will redo it inside soon
My biggest sins: 1. Don't apply Excel concepts to DAX; 2. think ahead and plan the hierarchy of your data and which tables are related to which; 3. understand the difference between calculated columns and measures; 4. DAX and Power BI are not the same: DAX is the language to for the calculation side of Power BI.
Thanks for sharing, big lessons there :)
More than 4 times for me. Learning DAX since 2010. You don’t learn DAX, you “learning” DAX. And yes, Calculated Columns and Related are evil especially in tables with many rows, especially portioned rows.
We should do a calculates column club 😂
Can't hear shit
Happy friday to you too 😊