DAX Fridays
Vložit
- čas přidán 5. 07. 2024
- In this video we are going to cover 53 (!) DAX / Power BI best practices.
Chapters:
00:00 Intro
00:40 DAX Functions best practices
03:00 Error prevention
04:00 Formatting
09:00 Maintenance
10:30 Naming conventions
11:45 Performance
End!
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
So, I’ve been watching and learning from Ruth now for the past year and I have to respectively disagree with some of the negative comments today.
I have learned so much from her videos (nearly 1,500 videos on her channel!) in that time and always enjoy her friendly demeanor on camera who takes a lot of the mystery out of Power BI, especially DAX. You know a good educator when they make complicated things seem easy and Ruth excels at that.
On top of that, this is a community on her CZcams channel and is a great way for all users to share their knowledge in the comments section which Ruth frequently encourages - including today. She doesn’t pretend to know everything and always encourages folks to comment and share their knowledge.
Anyways, thank you Ruth for everything you do on this channel as I for one have greatly improved my knowledge of DAX in the part year from stepping through many of your examples. Keep it going in 2021 and beyond!
Thank you CJ, you nailed my intentions with the video to core! I really appreciate it 💛
If I had to guess, "Objects with no description" is referring to the naming of objects in the selection panel. If for example you add a bunch of buttons or images to your report page, they all have generic names - button, button, button, image, image image - and it can get hard to know which image is which when you might be editing them, moving them, grouping them, etc.
Oh I see, that makes sense, thanks!
One of the many, many reasons I make it a point to watch your videos on a regular basis .... @ 8:46 😂
I also loathe that PBI doesn't just make 0 decimals + thousand separators the default ....especially since it's their recommendation !
Thank you for all of your videos, I very much appreciate & would be lost without them :)
😂😂 Hopefully soon, thanks Melissa!
Month as string must be sorted - so that whenever you use it in x axis for colum chart, it is sorted as month like Jan, Feb..Dec and not in alphabetical order like Apr, Aug...Sep
Thank you Ruth. Possibles explanation for 2 of your questions :
- I always hide the fact table in the report view, we just need the measures from it.
- Month as string must be sorted by another column, otherwise, you will get April as the first month !
I love this way of you doing videos. It is fun going through the otherwise super boring blah blah with you
Wonderful to hear!
❤️ loving these best practices.
Enjoy!!
Loved the comment - HOW YOU DARE (pick on Power Query)!! Same sentiment :D :D
😂😂
Came from the bottom of my heart!!
Understanding the context of the set of rules helps to understand the rules.
This set of rules is made for the Best Practice Analyzer within Tabular Editor. It's a fantastic tool allowing to quickly analyze a model and easily correct some mistakes. Rules don't concern only performance. It concerns also readability and usability for business users. The rules have not the same importance. Each model builder can choose the ones to be mandatory, optional or not relevant for his use case.
Some more rules are missing but the BPA is highly configurable. It is possible to add your own rule or disable a rule you don't want. Definitely, some rules need more explanations.
Thanks for the explanation!
I'll try to answer some of your questions:
FORMATTING:
- Hide fact table columns & Hide foreign keys > I guess that's to allow access to them through measures. By that you can do the analysis based on categories provided by Dimension Tables and calculations by measures.
- Month as a string should be sorted > Because any visualization with Months as a category will be sorted alphabetically. That's why Month Name column should be sorted by Month Number.
Thanks :)
Hide fact table columns @ 5:05
I actually successfully did it on my model with 12 fact tables, soon to be 14 pending my boss’ approval, measuring a plethora of operational metrics. You need to create many dimension tables and it’s a lot of work but it’s fun because I really got exposed to fact and dimension tables. The result for my model is almost 30 dimension tables but all the fact tables are hidden. I did it after I related all attributes to dimension tables and writing measures. To that point, another good practice is that only dimension tables should be in slicers. It also avoids some complaints from report consumers, i.e., when they use the wrong dimension or those exclusive to one fact table only 😂
The 5th one (referencing another measure directly) is a must if you need to show the same measure results in different decimal prescion.
@8:45 LMAO 🤣
😂
I laughed watching this with you! Particularly you tutting about power query and transformations. Very honest and fresh.
😂😂 Whoever picks on power query, picks on me too ;)
Thanks Ruth!! Supernecessary.. short DAX tips & tricks Great video!
Thanks!!
Hide fact table columns: If you use Dimension Tables, use that column in the report and hide that relationship column in the fact table is what they mean 😊👍🏻.
Oh, so they mean to hide relationship columns? Got it :)
Hi Ruth,
"Hide fact tables column": I think that your fact table should ideally only contain foreign keys and fact numbers (like quantity, or price). And if you use as a rule never to use a table column in the Value field of a visual but always a Measure, then you should be able to hide all the columns of your fact table. So only use explicit measures and never implicit measures. By the way if you want to use Calculating Groups you cannot have implicit measures (=table column in value field of visual).
"Month (as a string) should be sorted": I simply think that they want to indicate that your month names would (by default) be sorted alphabetically and therefore you should sort it based on another column of your calendar table (the month number).
Rarely I work with fact tables that only contain numeric values, bit it has happened, so I understand for those cases.
Thanks for sharing!
It would be nice to have a link to these 53 rules that she is referencing.
Thanks Ruth
My pleasure:)
Great
Yey!!
'Hide fact tables' is an interresting one that I try to understand. It's probably very relevant for huge fact tables.
I suppose it can be spitted into these rules :
a fact table must not contain text (text must be moved to dimensions)
a fact table must contain keys instead of text (keys must be hidden)
as it is better to use explicit measures, numeric columns must be hidden
measures must be moved out of fact tables
report builders would better use aggregated tables than fact tables
...
and then hide fact tables
To make it easier to understand you can read full descriptions form the original file:
raw.githubusercontent.com/microsoft/Analysis-Services/master/BestPracticeRules/BPARules.json
Since it's a simple json file you can use Power Query :-) to reformat it like this.:
pastebin.com/G6e6QnA0
Some rules make much more sense with full descriptions.
Also for model rules this article is a must read:
Understand star schema and the importance for Power BI
docs.microsoft.com/en-us/power-bi/guidance/star-schema
Not only it explains what are the best practices but also why (like why star schema is usually better than snowflake).
LLevar a cabo estas 53 mejores practicas es difícil o quizá hay algunas que ni si quiera las conozco (mdx), pero en general la mayoría trato de seguirlas y es bueno saberlas. Saludos Riuth y buen fon de semana.
Ya, verdad?
in Power Query can we split one sheet into many sheets?
If you are using Excel, you can create a pivot and then separate it in the analyze menu, close to the pivot table name.
Percentages, as with any other numeric format should be appropriate to the specific content of the measure. How they come up with 1dp as a standard I have no idea...
There is a lot of wisdom in your comment....
Now that’s something I don’t often hear 😂
Interesting stuff but I tend to use "USERELATIONSHIP" insetad of "TREATAS".
Are they actually the same thing or TREATAS is better?
For the "Month as a string" must be sorted, I'm guessing also but I faced a problem sorting "Jan", "Feb" in visuals because Power BI seem to recognise them as text strings and sort them by alphabetical order (i.e. April and August are the first months) even though I have formatted as date. So I think must have an adjacent column to identify the months as month-numbers so the sorting can be correct.
My understanding is that TREATAS performs better.
Regarding month as a string, why say month and not calendar items? Is something going on with month we don't know about?
Hello, Great Video Ruth!!! The conclusion from their best practices seems to be: Reduce and avoid usage of Power BI. There are so many restrictions that it is really hard to build something useful in reasonable time following all these rules. And one more specific thing from the first set of rules: Maybe "Measures should not be direct references of other measures" means that there should not be measure name nested inside other measure. The whole logic should be rebuilt from scratch with only table column references. Have a nice weekend.
😂😂😂 I think those best practices are mainly for big datasets.
With your second part you mean that you should not refer to a measure in another measure? In that case they have changed the rules in the middle of the game :(
@@CurbalEN I quite often use the reference to a measure in another measure, but I saw the video by Alberto Ferrari where he explained that in many cases it leads to double context transitions that affect the DAX performance. From the other side it is much easier to service the whole model when you use direct measures references - when you need to update the basic measure you just do it one place and only ones. Have a nice weekend.
Exactly, that is what I have been doing all along....it is crazy...
I understand your first sentence better now.
@@CurbalEN It's explained in the description:
"Description": "This rule identifies measures which are simply a reference to another measure. As an example, consider a model with two measures: [MeasureA] and [MeasureB]. This rule would be triggered for MeasureB if MeasureB's DAX was MeasureB:=[MeasureA]. Such duplicative measures should be removed.",
Hi
Can I ask you to make a video about connecting Power BI to Dynamics Nav and refreshing data with gateway
I'm your follower and I learn very much from your videos.
Thank You
I only have customer access to Nav, so I am afraid I can't do a video on that.
Looking forward to someone making a a similar Best Practice list for 'Power Query'.
🤔🤔 Great idea!
Btw, what is your favorite best practice?
Months as a string sort alphabetically - april and august come first etc. - you should always put in a "sort-by" numberic column or your visuals will look silly
I get one month as string must be sorted but with a numeric colum or April Will be your first month filter value.
5:22
Hide fact table columns - the idea here is to hide those columns in 'model' view AFTER you've linked all the necessary relationships. And provided you do not need those fact columns in visualisations. Then you can hide them to make everything more clear in the Fields pane.
I might be wrong, though. I just started this software.
Wouldn't that apply also for dimension tables? Why only factor tables?
Month must be sorted: if you have acolumn (name it "Month and Year") like =FORMAT(Date,"mmm-yyyy") the result is "mar-2021" for example. When you put it like so in a matrix Power BI reads is alphabetically. Like Apr-2021,Aug-2021.Dec-2021 etc... that's why it is a metter to have another column (name it "Sort") which is like =FORMAT(date,"yyyymm") and sort "Month and Year" by "Sort". This is how i get it.
Hello mam i m from india..... I have completed excel and vba... So what should i do after that? Thanx
Measures are portable and evaluated based on their filter context. There is no real need to directly reference a measure nor to have two measures that have the same definition because the single defined measure can be used wherever needed. The only way this might have value would be if you were defining a dataset and wanted to put the measure in two separate locations so that someone building a report off that dataset might better understand how to utilize the measure when creating visualizations, but proper modeling methods should eliminate that need in most cases.
I had to reference a measure recently because I needed to have different decimals on different visuals due to default round up on decimals. :(
Can non IT'ian person learn dax....please reply n how
Practice, practice and practice.
There are no shortcuts here.
@@CurbalEN thanx maa'm
Month as a string must be sorted....yes by a monthnumber column otherwise it will show alphabetically which makes no sense....maybe this was meant?
Maybe, but why talk about only month column instead of calendar columns in general or text columns?
@@CurbalEN I suppose it's because the problem occurs often with months. If we understand the problem with month, it is easy to apply corrections to other columns like days name. An other reason is may be they cannot detect easily other columns concerned by the problem.
Let's not forget these rules are made for the BPA analyzer within Tabular Editor. Some more rules are missing like day as a string. In the BPA, it is possible to add your own rule or disable a rule you don't want.
Thanks for the comments Didier.
Two measures should be unique names.not conflict with name measures
Oh, it is referring to the names? Can you create two measures with the same name? I though I was not possible?
Yes we don't giving same name more than on measure
‘Ensure tables have relationships’ - erm what??
🤷♀️🤷♀️🤷♀️
If you're inviting us to see your content (which I'm always happy to see) please try to read the material first and maybe prepare the answers before recording. Watching this movie felt like wasting time ..
There is no more material than what you saw. I can not read what it is not there and I say that clearly in the video.
Of all 53, I didn't know 4-5?, how is that making the entire video not useful?
@@CurbalEN correct!
Thanks dear! It took me 4 hours to put that together!! Not fair to bash it like that.
Some rules are really not clear. I am not sure spending many hours more on this topic will make all the rules clear. Sometimes, you need to read the script behind a rule to understand it's meaning. Ruth helped to make it clearer in a 20 minutes video. Thank you Ruth
Thanks Didier, if they were not clear for you either, then I never had a chance!!
Why do you talk about it when you don't know half of the things!
Half the things is a bit of an exaggeration 😉.
Happy Friday to you!