This is a post about a post about a post. Thanks to those of you who are entering comments in the original May 12 post titled SQL, M or DAX? This is a popular topic. And thanks to Adam Saxton for mentioning this post in his Guy in A Cube Weekly Roundup.
We live in a world of choices and we have many tools at our disposal. In Microsoft Business Intelligence solutions using tools like Power BI and SQL Server Analysis Services, you have at least three different ways to perform data collection, transformations and calculations. A question I get all the time is which database or BI tool should be used to perform routine tasks. Is it best to shape and transform data at the source, in Power Query using M script, or in the data model using DAX?
In this series, I’ll demonstrate options for creating utility and dimension tables, columns and calculations using each option and discuss the advantages, disadvantages and recommended practice for each.