You Shouldn't Use DAX for That

[Greg_Deckler]

Introduction

I have some grievances and while it may not quite be Festivus just yet, I'm going to air my grievances anyway. My most pressing grievance is around the phrase, "You shouldn't use DAX for that." You see this phrase all the time in the forums. Question comes in about how to do a particular calculation in DAX and someone responds "You shouldn't use DAX for that, use Power Query." Well, as you might have guessed, "I've got a lot of problems with you people...and now you're gonna hear about it." 🙂

Calling people idiots

The first problem I have with starting out a response with "You shouldn't use DAX for that." is that this is essentially the equivalent of stating "You are an idiot and don't understand what you are doing in the slightest nor do you have even a single clue about generally accepted best practices." First, it's not nice to call people names or tell them they are idiots. Second, you may be the one that is being an idiot, or at least incredibly arrogant and presumptive.

Think about it, what if they have to use DAX? An extremely popular usage model is one where business users work against Live datasets in Power BI. In these circumstances the business user doesn't have access to Power Query to change what is going on in the model, they are by-and-large relegated to using DAX measures to make any changes, etc. Sure, they could go back to whoever created the data model and ask them to modify it and six months later they will have their required changes. Not helpful. 

But even in Import and DirectQuery situations is it always advisable to use Power Query instead of DAX? Not necessarily. Any reasonably complex data transformation performed in Power Query has a good likelihood of breaking Native Query functionality. As a side note, the Native Query functionality pushes processing back to the source system and thus data loads tend to be faster if you don't break Native Query functionality. If you break Native Query functionality in a query the query has to use local resources on your desktop or gateway to perform the transformations. Now, in import-mode perhaps this is less of a protential problem if the speed of a single table load slows down. Although, there are situations where this could be highly problematic on an extremely large fact table with tens of millions of rows and end up exceeding the desired refresh window. More importantly, for DirectQuery models this could be disastrous. Here you have a measure or column used in a single visual that you could do in DAX but by doing it in Power Query you break Native Query functionality and end up slowing down every single visual in the entire report. Not smart.

By your own logic, you are also wrong

This idea of "You shouldn't use DAX for that" comes from a generally accepted best practice of pushing data transformation and calculation logic "up the data transformation chain". This is why the words "You shouldn't use DAX for that" are inevitably followed by "You should use Power Query". The logic here is that Power Query is further up the data transformation chain and thus you are transforming the data before it gets into the data model. Now, the first issue is that generally accepted best practices are just that, general. See specific cases cited above where generally accepted best practices might be bad. The second issue is that by this same logic, the phrase "You should use Power Query" is equally incorrect. Under this logic, Power Query is as equally bad as using DAX because what you *should* be doing is performing the data transformation and calculations at the source. See, it's the same principle. If it is wrong to do it in DAX then it is wrong to do it in Power Query.

Conclusion

Be nice, don't call people idiots. They just might know more about their needs and requirements than you do. At the end of the day, there will be people that neglect to include important details when asking questions. Other people will be arrogant, presumptive and internally inconsistent with their recommendations. The sun will rise tomorrow to witness more of the same.