If you’re working with Power BI, you most likely hate it or love it. And whether you hate it or love it depends on the stage you’re currently in. And the stage you’re currently in, depends on the time and effort you’ve put into it. Makes sense, right?
Power BI of course has many aspects, but let’s today focus on one of the hardest: DAX. You magically have a great dataset that is refreshed regularly, you’re working in one of the few organisations where the dataset is accompanied by proper and up-to-date metadata. Also, you don’t have to worry about how to share and where to upload the report and so on. You just have to make a wonderful report, which gets the most out of the available data. Besides great visuals, you need DAX. A lot of DAX.
Everyone working with DAX follows the same 5 stages. Many people stop, frustrated, during stage 3. But once you’ve put a lot of effort in Power BI and you’re past this point, a world of joy and lots of potential is awaiting you!
Let me describe the 5 stages
Joy & Happiness. Power BI is initially intuitive: you can easily create nice bar and pie charts, like in Excel but easier and fancier, and by clicking some visuals the whole report changes accordingly! The sums, counts and aggregate formulas you’ve created with DAX work properly, just like they did in Excel. Actually, it feels like Excel 2.0 and your Power BI future looks bright.
Deception & Frustration. The results of your formulas that involve more than a simple sum, count etc. often don’t make sense, but well... sometimes they do. You’re just trying some formulas till you’ve got the desired results. But once added to a different visual or with a different filter, the formula doesn’t work anymore! There must be a glitch in the tool! DAX is just behaving weirdly today. The difference between scalar and tabular functions are kind of clear to you, but not exactly. You also don’t understand the difference between a calculated column and a measure. Sometimes the columns work, sometimes the measures do, so you just go for the one that works that day.
Disillusion. None of your more complex measures seem to work anymore. In any other language like SQL, R, Python or with Excel, you exactly know how to get to the right results. But with DAX, it just doesn’t seem to work. Why does DAX have to behave so differently!? Even the simple calculate function does not always work as expected. And what’s the difference between filter and calculate!?!? Worst of all, sometimes your formula does yield the right results, but is so incredibly slow and you just don’t have a clue what to do about it.
Lift-off to success. Congratulations, you've probably spent quite some time and effort in learning DAX and it has paid off. You do understand the more complex DAX functions like iterators, time-intelligence functions, and you can create formulas that mostly work. Sometimes you’re creating a monster of a query, which is very slow, but that has nothing to do with your code, right? It’s just the amount of data. Also, you know how to Google for funky out-of-the-box DAX queries that open up a lot of additional perks of Power BI such as: adding measure filters to a visual, transform a slicer visual into “and – and” logic instead of “or – or”, use measures for a parameter slicer, use measures for conditional formatting etc. You’re making big steps now!
Master of DAX. You don’t just write code, you own code! You understand what you’re doing and why you’re doing it. Evaluation context and filter context makes complete sense to you. The complex codes yield the right results and if not, you’ve got the skills to troubleshoot and fix it. You’ve also got the skills to tweak DAX in order to enhance performance. You write reusable codes, and therefore your reports are much more efficient and stuff that you never expected to be possible with PBI is now possible.
In a nutshell: DAX is complex! A quote from the absolute heroes of DAX, Marco Russo and Alberto Ferrari, “DAX is easy to learn, but hard to master”. That’s why it’s not something you can “just do on the side”. Nor is it possible to manage with just some people in your organisation that “sometimes develop a report besides their regular task”. You need dedicated people, people who can spend the time and effort to master DAX. People who discuss with peers, people who learn from the unlimited resources on the internet, read books (especially “The definitive guide to DAX”), use additional tools like the performance analyser, query plans, and DAX studio to tweak and improve the code.
You are not alone in thisjourney, just request the help of a professional. Get in touch with us and we will discuss the best way forward for you. We offer trainings tailored to your level, create reports with proper documentation so you learn from our work and will be able to reuse the code, or we review your previous work and improve existing products. Let’s get the most out of your data!
Comments