Keeping your DAX neat and tidy
It doesn’t matter if it is a drawer, room or a Power BI dataset, it is horrible to open any of these and finding a mess; then, you have to spend time searching for things that are not where they suppose, trying to decipher what the hell does anything mean, and in general wasting valuable time in cero value activities.
I consider a fundamental virtue of any BI professional always to aim to make a model that can be easily understood and worked by other users. We can perform multiple activities to achieve such a goal. Below you will find my top five.
1. Please, please, please!!! Format your DAX formulas properly. It is so beautiful to see well-formatted DAX and the best thing is that it is not rocket science, I admit it takes some practice to get used to doing it, but if you can press the TAB, ALT and Enter keys, then you have everything it takes. DAX Formatter is a great tool to learn to write “Sixteenth Chappel” grade DAX code.
2. Create a Measure Table, or as I like to call it: keep the things in their damn place!! Avoid having DAX measures all over your model and create a table that holds your measures; this way, you will always know where to search. Want to know more, see this blog from Matt Allington.
3. Create basic formulas and iterate through them. This means starting with basics and then using the almighty “Calculate” or any other DAX to change the context. This will allow you to have consistency among calculations.
4. While naming your measures, create an “index” structure; when your model grows, it will help you to find measures quickly. To create an “Index structure,” you have to add some numbering (or letters) to the beginning and group the formulas by subjects; in my experience, I have come two to setups:
a) Numbers: I start the name the DAX measures based on numbers, this is ideal when we have a nice Star Schema and
b)Alphabetic: Best suited for Snowflakes, the beginning of the name mentions the name of the table where the measures come from.
4.1 Mehdi Hammadi shared a great tip (see in responses) I was not aware of it: store the measures in folders! Mindblowing!🎇😲🎇 Thanks Mehdi for such a valuable input! (See in comments.) I found the next RADACAD article about how to do this.
5. Please write meaningful names on your measures. What the hell is the difference between “Sumofhours1” and “Sumofhours2”? Isn’t it better to write: “00.01 Sum of Hours” and “00.01 Sum of Hours — Vacations”. Don’t mind the names are too long, remember you can always edit them in the visual.
It isn’t that hard, right? Having a tidy DAX will allow you to be more efficient with your work, and you will have happy clients and end-users. Now, open your PBI Desktop and start writing the DAX that would make Michelangelo cry because of the beauty.
Oscar Martínez - Business Intelligence and Financial Planning Consultant - Sereviso / Entech SPA AG…
Integrity has been the cornerstone of my professional development. Results driven, customer-centric, analytical, and…