1:29 Well. Marcos and Albert's Dax book has some rules. Format the query or even use : when is a measure. But, yes this this option also applies. Actually, the idea of using space between braquets like ( Table[name] )... Dax formatter provides ideas
It means terminology used by the business, so instead of tbl_sales you write Sales or CustomerName, you put client ( if the business use client instead)
one missing: "Integer in fact tables; string in dimension table" (not DAX related, but important); you can dramatically reduce the size of your model by this
Thanks for useful advices. I knew that two direction relationship could cause problem with formulas, but it never cross my mind that I should change it into one direction myself. Somehow, I taught it was ok since it was turned on by default.
Thanks Ruth! I had to learn some of these the hard way, but I am happy to report I am pretty good at following these rules. I need to get more consistent with commenting my formulas. :-)
1. Format all DAX 2. Comment all DAX 3. Use explicit measure > implicit measures 4. Use measures before calculated columns 5. 1. Calculated column = Table[Column] 2. Measure = [Column] 6. Model as a star schema 7. Avoid Bi-directional relationships 8. Don’t use table for measures 9. Use Business Names everywhere
Personally I want measures table. It helps you to stay organised. It is a best practise in almost every good power bi course on udemy. Additionally, when creating both calculated columns and measures you must use the fully qualified reference [table]{column name}. Otherwise you might refer column with the same name from another table. Last i think naming tables with suffix as “fact” and “dim” is the best thing one developer can do, especially when they are collaborating with other people. Thank you for that video! And for all the hard work! All due respect, Atanas
Agree with most. Calculated columns can be an appropriate performance improvement and if so, a calculated column is better than a slow report. But if possible I'd also prefer creating the column in PowerQuery. Then, using the auto-sum build-in measure gives you a build-in view of a table with the underlying records which you have to build manually as a drillthrough page when using a dax measure. Some users like it. I totally agree on the business names. If you stick to all other rules - star schema, dax measure in fact tables - you see anyway from the icons which table ist Dim and which one is Fact: The one with the calculator icon.
Well, I still use "Related" function to have higher granularity than numeric key in the fact table. It is life-saver when troubleshooting data quality errors in production (even usage of related is not advised)
I have started to add comments only recently, but agree I wish I'd been doing to for longer. regarding the last point, I love a measures table, and as to naming, I'm just old so having spaces in names or no prefix just seem wrong. One thing about names with spaces in power query, on more than one occasion, I've not had a name recognized because I'd written "abcd " rather than "abcd" or vice versa.
@@CurbalEN fair enough, but I still find it strange not having to Join_things_up. Mind you I can remember floppy discs when they were FLOPPY, love the channel. ☺☺
Why do you hate measure tables? I just started using them and it seems to be working pretty well so far. Previously I was creating the measures within the data tables but that was a pain to organize even with a subfolder as I couldn't create the measures directly in the subfolder and had to keep manually moving them. I guess I could switch it around and put the columns in the subfolder instead though.
@@CurbalEN I'm not sure I follow you here. We use measure tables and then organize the measures into folders. I find that easier to follow then digging through all the fact tables and looking for measures there. Could you expand a bit here?
@@CurbalEN Yes, also there's a difference in performance too especially faced with my large model, and tested too to see that significant time difference when a measure is created in measure table versus fact table.
It was also suggested that when you hide all columns leaving only the measures visible, a measures folder is automatically created with the measures icon displayed. This solution was promoted as a best practice as well. So I guess “best practice” in this case, is whatever works best?
I agree that technical names don't belong in powerbi, but a dim and fact at the beginning of the name help me understand who is who when I look at a table list all nicely arranged. Unless until there are table folders. When there are then dim and fact can go.
@@CurbalEN ok I see I'll have to change my ways... I'll see if putting fact and dim under description or synonym helps me filter those when necessary. You have any naming convention for calculation groups?
7:50 another one haha. Don't say that in front of Alberto. Actually, there was a live stream SQLBI and Guy in a Cube where they talk about it and Adam pointed out Alberto and Marcos Dax book. So, 😜
@@CurbalEN if we talk about not formatting dax being a giveaway of a beginner variables are part of the pack, aren't they? also they perform better in many scenarios
@@CurbalEN now that I'll change table names to business names I think we agree on everything, but I'm curious on how you decide on which table you put complex measures that combine say two fact tables. Just random?
Very good one on DAX. I would like to also know When to create a Calculated Column and a Measure. Can we always create a Calculated Column or Measure irrespective of the context?
Sorry Curbal, I agree with most of these rules but I think labelling people based on them is far from how the real world works (The only rule I follow strictly is the one explicitly stated by Marco & Alberto...If it is not formatted… it is not DAX!...and that is because it is always true). Reality is that the code and build are always the best giveaways for a noob, not rules because in the real world only results and deadlines matter.
As one who's been doing BI for 20 years, I completely disagree with the rule #8. Also, if I see more than 2 DAX columns in a table, I'll throw your resume away. DAX columns serve no purpose - they don't even react to slicers, they are only there to cover up for lack of PQ and data modeling skills. You DAX columns belong in Power Query.
Thanks Vlad, power bi is used by professionals like you but also by business people as a self service tool and if your background is excel, you will naturally create a lot of calculated columns. I did that. All the time. Got what I needed and run. ;)
I like having a measure table so all measures under one roof. nicely structured and organized...
1:29 Well. Marcos and Albert's Dax book has some rules. Format the query or even use : when is a measure. But, yes this this option also applies. Actually, the idea of using space between braquets like ( Table[name] )... Dax formatter provides ideas
I almost fell off my couch when you said you hate measure tables!
I’ll forgive you though as your content is so good.
I forgive you too 😂😂
I couldn't imagine having 200 measures in my fact table either.
As usual, another great summary of Power BI tip/advise that I have always enjoyed and come back to your channel. :-)
More to come!
9:16 What does business name mean please?
It means terminology used by the business, so instead of tbl_sales you write Sales or CustomerName, you put client ( if the business use client instead)
@@CurbalEN Thanks, Ruth. Got it now. So, in another words, avoid using IT or techie terminologies.
Exactly!!
Wonderful insight. I need to work on formatting and commenting. Thank you so mucj
My pleasure :)
Happy Friday Ruth, this is really a best practice. It makes life easier.
Happy Friday Danny :)
one missing: "Integer in fact tables; string in dimension table" (not DAX related, but important); you can dramatically reduce the size of your model by this
Thanks for useful advices. I knew that two direction relationship could cause problem with formulas, but it never cross my mind that I should change it into one direction myself. Somehow, I taught it was ok since it was turned on by default.
Same for me when I got started. Learned the hard way 😬
Thanks Ruth! I had to learn some of these the hard way, but I am happy to report I am pretty good at following these rules. I need to get more consistent with commenting my formulas. :-)
Yey!! Well done :)
1. Format all DAX
2. Comment all DAX
3. Use explicit measure > implicit measures
4. Use measures before calculated columns
5.
1. Calculated column = Table[Column]
2. Measure = [Column]
6. Model as a star schema
7. Avoid Bi-directional relationships
8. Don’t use table for measures
9. Use Business Names everywhere
Personally I want measures table. It helps you to stay organised. It is a best practise in almost every good power bi course on udemy. Additionally, when creating both calculated columns and measures you must use the fully qualified reference [table]{column name}. Otherwise you might refer column with the same name from another table. Last i think naming tables with suffix as “fact” and “dim” is the best thing one developer can do, especially when they are collaborating with other people.
Thank you for that video! And for all the hard work!
All due respect,
Atanas
We will have to agree to disagree on all points! 😉
Great tips. Thx a lot.
Hi Ruth,
Great content.
As per your experience, how many columns an optimized Power BI model/ table should have?
I dont think you can isolate that, performance has many variables and the answer will change from model to model.
Agree with most. Calculated columns can be an appropriate performance improvement and if so, a calculated column is better than a slow report. But if possible I'd also prefer creating the column in PowerQuery. Then, using the auto-sum build-in measure gives you a build-in view of a table with the underlying records which you have to build manually as a drillthrough page when using a dax measure. Some users like it. I totally agree on the business names. If you stick to all other rules - star schema, dax measure in fact tables - you see anyway from the icons which table ist Dim and which one is Fact: The one with the calculator icon.
Thanks Martin
Great film. You look very nice on this white blouse.
Thanks Anna, it is a knitted sweater. It is like 5 degrees in sweden 🥶🥶
Well, I still use "Related" function to have higher granularity than numeric key in the fact table. It is life-saver when troubleshooting data quality errors in production (even usage of related is not advised)
I have started to add comments only recently, but agree I wish I'd been doing to for longer. regarding the last point, I love a measures table, and as to naming, I'm just old so having spaces in names or no prefix just seem wrong. One thing about names with spaces in power query, on more than one occasion, I've not had a name recognized because I'd written
"abcd " rather than "abcd" or vice versa.
Power Bi is meant to be used by business users and therefore the names should contain spaces. Easier also when visualising the data.
@@CurbalEN fair enough, but I still find it strange not having to Join_things_up. Mind you I can remember floppy discs when they were FLOPPY, love the channel. ☺☺
Thanks 😊!
What are "Business Names"? I've never heard that term in relation to the naming of tables.
Names that business users use when naming things
Could you please explain more about #1? Why shouldn't we use "table" in measures?
It is only for readability. If you add table to a measure name, you will not know if it is a measure or a calculated column by just looking at it.
Why do you hate measure tables? I just started using them and it seems to be working pretty well so far. Previously I was creating the measures within the data tables but that was a pain to organize even with a subfolder as I couldn't create the measures directly in the subfolder and had to keep manually moving them. I guess I could switch it around and put the columns in the subfolder instead though.
It is good for the developer bad for everything and everybody else 😞
@@CurbalEN I'm not sure I follow you here. We use measure tables and then organize the measures into folders. I find that easier to follow then digging through all the fact tables and looking for measures there. Could you expand a bit here?
I explain it here: (min 9:30)
th-cam.com/video/zrqKthwDMok/w-d-xo.html
Also Q&A won't work.
@@CurbalEN Yes, also there's a difference in performance too especially faced with my large model, and tested too to see that significant time difference when a measure is created in measure table versus fact table.
Oh really? I didn't know that, but it is definitely good to know. Thanks for sharing :)
It was also suggested that when you hide all columns leaving only the measures visible, a measures folder is automatically created with the measures icon displayed. This solution was promoted as a best practice as well. So I guess “best practice” in this case, is whatever works best?
I guess we all have our preferences!
Agreed.
En todo de acuerdo, genial!
Gracias!
Like the way you talk
Thank you :)
Just a doubt, i cannot download your PBI Files to pratice my self without become a paid membership?
I dont have any paid membership, all my files are available for free for everybody.
Curbal.com - Resources - Download center
3:49 Well..."unwritten"... Ehh, no haha. Don't say that in front of Marco It's on the book and Marco always said if it's not formatter it's not Dax. 😜
It's an unwritten rule written by Marco 😄
I agree that technical names don't belong in powerbi, but a dim and fact at the beginning of the name help me understand who is who when I look at a table list all nicely arranged. Unless until there are table folders. When there are then dim and fact can go.
You can use technical terminology during development, but once you press publish, they gotta go ;)
@@CurbalEN a report that is really used is always in development 😀
Good point, but you owe to your users those business names ;)
@@CurbalEN ok I see I'll have to change my ways... I'll see if putting fact and dim under description or synonym helps me filter those when necessary. You have any naming convention for calculation groups?
7:50 another one haha. Don't say that in front of Alberto. Actually, there was a live stream SQLBI and Guy in a Cube where they talk about it and Adam pointed out Alberto and Marcos Dax book. So, 😜
Christopher Adamson "Star Schema-The Complete Reference" A must read!
Thanks for sharing!
@@CurbalEN When I retire you can have my copy :-)
Maybe star-schemas are not relevant by then!
@@CurbalEN They will rule until you retire :-)
Not so sure about that, AI is around the corner and it can manage unstructured data!
We can call it: DAX Almost Fridays. It sounds good!
😂😂
Somebody who never uses variables for complex calculations is probably a New to dax as well. Now, should we put two underscores before variable names?
You can do that, only developers will see it :)
@@CurbalEN if we talk about not formatting dax being a giveaway of a beginner variables are part of the pack, aren't they? also they perform better in many scenarios
They definitely are !
@@CurbalEN now that I'll change table names to business names I think we agree on everything, but I'm curious on how you decide on which table you put complex measures that combine say two fact tables. Just random?
I just put them in the table where I think a business user would go and look for. If unsure, ask them ;)
DAX Friday - on a Thursday... :)
I know ...😂😂
yes what´s next? Weekend on Monday? :-) Bananas in a bottle?
Totally agree about "Identify relations automatically"... This default should be banned for eternity.
😂😂
Hahaha almost Friday 🤣🥳
😂😂😂
Very good one on DAX. I would like to also know When to create a Calculated Column and a Measure. Can we always create a Calculated Column or Measure irrespective of the context?
For that, check this video:
m.th-cam.com/video/SmXLgEHXSGc/w-d-xo.html
According to this, I'm a veteran, so why do I still feel like a beginner?!:-)
Arent we all ? 😉😉
I like measure tables 😢
Many do 😔😔
@@CurbalEN Haha, join us, one of us, one of us 😜
I cant , I really dislike that practice :(
I'm a beginner! You are so sweet Ruth 😜
Hope these tips help!!
Bahahahahah! I would think the commenting is more of a best practice, not a sign of a veteran developer.
You first need to know how to comment and then why you should comment. Beginners never do imo 😂😂
“Give you away”
Pure spanglish?
Sorry Curbal, I agree with most of these rules but I think labelling people based on them is far from how the real world works (The only rule I follow strictly is the one explicitly stated by Marco & Alberto...If it is not formatted… it is not DAX!...and that is because it is always true). Reality is that the code and build are always the best giveaways for a noob, not rules because in the real world only results and deadlines matter.
As one who's been doing BI for 20 years, I completely disagree with the rule #8. Also, if I see more than 2 DAX columns in a table, I'll throw your resume away. DAX columns serve no purpose - they don't even react to slicers, they are only there to cover up for lack of PQ and data modeling skills. You DAX columns belong in Power Query.
Thanks Vlad, power bi is used by professionals like you but also by business people as a self service tool and if your background is excel, you will naturally create a lot of calculated columns. I did that. All the time. Got what I needed and run. ;)
@@CurbalEN Agree when that is applicable. I however never met a business person who does their own reporting. Must be me and my excellent products LOL
You are spoiling them!! 😊
Please, someone, write these rules down.
Now they are ...kindof...😉