DAX is simple, but it is not easy

Inspired by this great video and as a follow up to my last post regarding the Mastering DAX workshop I want to tell you about my findings regarding the DAX language:

DAX is simple,

but it is not easy.

This is so true. When I was sitting in the classroom, I realized in every example we did how true this statement is. If you see a working DAX formula you are tempted to say: “OK, no problem, this is easy”. But making a working DAX measure – which is really working all the time – no matter what the user is doing in terms of filtering and grouping is hard work.

If your measure is working in one situation but not in all situations

then your measure was wrong from the beginning.

Again, in many cases it seems that a measure is working. But as soon as you are changing some parameters or add an extra step of complexity it is not working at all. In many cases this means go back to start and do your work again.

Stick with the basics

Don’t bother about sophisticated functions.

Put all your efforts into filter context, row context, iterations, and context transition.  I am still learning the hard way that this is the only way to success.

You can’t learn DAX by copying formulas

or patterns from the internet or books.

I tend to learn new things by browsing the internet. If I find a solution which is pointing me in the right direction, I usually succeed in solving my problem. With DAX I first thought ok cool do it in the same way at sometimes it seems to work but unfortunately other times not. So learning the basics is so important not just insert some CALUCULATE command here and there.

You can’t learn DAX by reading books

Only by practicing.

I have bought quite a few books in the past and by studying them I was fooling myself. I thought I know enough about DAX now. But the truth is I knew nothing. As soon as you are doing real live examples you will realize that without a solid understanding of the basics you are caught in an endless try and error loop.

So having learned all of this I feel at least in a position which points me in the right direction.

With every project I will gain confidence but nevertheless frustration will be my good companion for quite some time.

Stick to the basics

and try to think the DAX way.

Mastering DAX workshop

Obwohl ich nun schon wirklich lange möglichst viele Bereiche zum Thema Power BI verfolge, ist die DAX Sprache nach wie vor herausfordernd für mich. Wobei mittlerweile denke ich, das geht jedem so…

Daher habe ich als Auffrischung unlängst den Mastering DAX workshop der allseits bekannten SQLBI Helden besucht.


Ich habe nun den Vergleich zum ebenfalls angebotenen Online Course und einer Schulung in „echt“.

Ich muss sagen, da liegen wirklich Welten dazwischen. So viele Kleinigkeiten, die in DAX aber sehr große Auswirkungen haben, können einfach nur face to face bewusst gemacht werden.

Die für mich wichtigsten Erkenntnisse sind zwar nichts Neues, aber man muss sie diese egal auf welchem Level man sich in DAX bewegt immer wieder vor Auge führen:

Obey the rules

Es gibt zwar oft mehrere Wege, um eine DAX Formel zu erstellen, aber im Prinzip fast immer nur eine richtige Lösung.

  • The filter context filters data
  • The row context computes data

 (nothing else)

Heißt einfach übersetzt, stimmt das Ergebnis einer Formel nicht, ist zu 99,99 % das die Ursache des Problemes.

CALCULATE löst viele Probleme

Entscheidend dabei ist aber, das man sich immer wieder vor Augen hält, wann CALCULATE was macht, warum CALCULATE das macht und wann CALCULATE allenfalls gar nicht erforderlich ist.

  • CACULATE schafft neue Herausforderungen
  • Stichwort Context transition
    • Überträgt den aktuellen Filter context in einen äquivalenten Row Context

NEVER filter a table, always filter fields

Selbsterklärend hier geht es vor allem um Performance

Do I need Power BI Premium?

Data - Marc

Often I got the question from customers: „Can you assign my workspace to a premium capacity?“ But frequently they actually do not really need Power BI Premium. It remains to be a difficult topic to decide whether someone needs Power BI Premium or not. Therefore, I decided to setup a decision tree that helps to decide if you need Power BI Premium or not.

This decision tree highlights a bunch of Premium specific requirements and features like breaking the data size limits, XMLA Endpoints, unlimited content sharing and much more!

Ursprünglichen Post anzeigen 182 weitere Wörter

Azure IoT Hub and Power BI Streaming dataflows

Ok time for some serious Prototyping today. Based on the announcement stated below I had the perfect use case to finally test some of the functionality provided by Azure IoT Hub and Power BI Streaming dataflows.

Streaming dataflows in Power BI premium now available in public preview | Microsoft Power BI Blog | Microsoft Power BI

Some time ago I bought this cool DevKit after I had attended a Microsoft workshop in Munich.

An all-in-one IoT kit built for the cloud (microsoft.github.io)

The walkthrough in this article is very precise.

Connect an MXCHIP AZ3166 to Azure IoT Hub quickstart | Microsoft Docs

I was able to successfully install all the required software on my local machine. Same is true for the Azure part and so I ended up with this – A piece of hardware sending real time data to my Azure IotT Hub. How cool is that.

The Power BI part was the last thing to do. Again, I followed this well written article.

Streaming dataflows (preview) – Power BI | Microsoft Docs

Et voila I ended up with data which went all the way from my local machine to Azure from there to Power BI and back to my local Power BI Designer. I am still very impressed with the result. For me it was so much fun to glue all this stuff together. This really made me smile for the rest of the day.

Back on track – my learning journey

The first half year was very challenging for me. After a as always to short vacation I’m ready to start learning again.

Today I took another Fundamentals exam. Some questions are pretty basic some not. Nevertheless I like to take such exams mainly to get a feeling what is expected from new candidates in a certain field.

Microsoft Certified: Azure Data Fundamentals – Credly

Apart from that I will spend 3 days to refresh my DAX skills. I will attend the Mastering DAX Workshop offered by sqlbi.com. I know some of their video courses and the are brilliant. So I’m really looking forward to meet these experts in person.

Mastering DAX Workshop – Amsterdam – September 30-October 2, 2021 – SQLBI

Microsoft Exam DA-100

In 2018 I passed the MCSA BI Reporting exam which is no longer available.

So time to renew the Power BI part of this certification which nowadays seems to be Exam DA-100.

To pass the exam you should have spent some time with Power BI. As I’m more interested in data design, connectors and other technical stuff behind the scenes the exam was a bit of a challenge for me. Nevertheless it worked out…


Moving From C/AL to AL – Chapter 2

My programming career began a long time ago. Back then a wise guy gave me a copy of Visual Basic 6 Pro. Since then I have come a long way. Although I want to make clear that I’m neither a hardcore programmer nor a extraordinary talented geek. My goal was always to get the job done.

So after many years of programming in Visual Basic and even VBA I got in touch with Microsoft Dynamics Attain 3.60. My first impression of the C/SIDE programming environment was the feeling of a cutdown version of the Visual Basic IDE. So I felt immediately at home. And C/SIDE was very efficient for my requirements. But of course also a bit boring too. Soon I was playing with com-add-ins again and later there came .net interop. And this felt good. Besides of NAV I invested quite some time into SSIS and there I really liked the script task the most. You had the full power of the .net framework but in a simple and cut down environment. Visual Studio always seemed a bit overwhelming to me. The following years I lost a bit of my coding hunger. I was more interested in things like SQL, Database Design and later the Power Platform rise really got me excited.

Then a few years ago suddenly Microsoft NAV or BC began to change. Suddenly we had a solid three tier architecture. We were confronted with extensions V1 and V2. This got my attention but I was not totally on fire. But this has changed in the last few months.

I did spend some time with Visual Studio Code and my verdict was – Yes, I like it.

I did spend some time with AL and the new concept of extensions and yes again, I like it.

The cloud, Azure functions, the Azure Stack itself, yes once more, I like it.

Now I’m again on fire. I feel excited as there are some many new things to learn.

Sure not all I easy or straightforward. A lot of things feel much harder to achieve now with regard to the new boundaries and Microsoft’s cloud first strategy. Nevertheless I’m willing to follow this path. I like the concept of bringing different pieces of software together. Running an Azure function from NAV BC to create a word or excel file seems pretty crazy but nevertheless I like it. I almost feel young again. Is this a midlife crisis – maybe but for me it feels right and that’s what counts. You should love the work you do. And you should never loose your hunger to learn new things. So yes I’m ready for the challenge. Let the games begin.

Moving From C/AL to AL

Finally I was able to attend a two days online training in order to get a solid introduction into AL Programming.

I can highly recommend this course to any C/AL developer who want’s to get started with AL quickly.

The intend of this post is not to go into the details of this training. Rather I want to point out the my top take-aways:

1. Be prepared for the cloud (NAV BC SaaS). A lot if smart people mention this as the first rule whenever you have to make a decision.

2. Get yourself trained on the concept of Microservices as this is probably also a very good approach for extension design.

3. Learn about the Power Platform. The connection between NAV BC and the Power Platform is getting better and better. So you should at least consider using the Power Platform in certain cases.

4. Get familiar with Azure and Azure functions. My feeling is that you will need them more and more.

5. When redesigning any NAV C/AL solution in AL think not about coding challenges in the first place. Redesigning the solution is the real challenge.

6. Just because it is possible doesn’t mean you should do it.