Background
Let this mark the beginning of a new series on DAX Language. If you are trying to get your hands on this cunning Language, be my esteemed weekly guest. Before long, your DAX skills will be improved. One raised to the power of 365 remains 1. But 1.01 raised to the power of 365 is approximately 38. It’s my hope that beginners can use this new weekly series to improve tremendously.
If you have more experience with DAX, you are also welcome on board. I’ll appreciate your contributions, while I may also be able to paint the story in a different light and show you a different perspective.
What is DAX?
An acronym for Data Analysis Expressions, the Powerful language used to analyze data on Microsoft Excel’s Power Pivot, Power BI and SQL Server Analysis Services. We can also write DAX on some external applications such as DAX Studio and Tabular Editor.
I refer to it as cunning, because learning it requires attention, writing it requires even more attention. It can be full of surprises. Is it difficult? No. Is it easy? No. Alberto Ferrari famously quoted one of his students saying, “DAX is simple but it is not easy”. Paying good attention plus lots of practice is what helps, because here, experience counts a lot.
Components of DAX
Functions: DAX is a functional language like Excel (behaves differently though) with a library of over 300 functions that can be called to perform different tasks. Eg, to add up values in a column, the SUM() function can be called to perform that task.
There are over 80 functions that are on Excel and available in DAX performing the same tasks as they do on Excel. How they arrive at the result is different though.
Expressions: DAX is largely about functions but not all about functions. Functions create expressions but A simple expression like = 2 + 2 qualifies as DAX when used in the DAX tools. Even something as simple as = “Subscribe to our youtube channel“ is also DAX.
Operators: While using functions and expressions, they are mostly incomplete without bringing in operators. At the least, we must use the “equals to sign” (=) to write DAX in the main tools.
While there are also other operators like “greater than” (>), “less than” (< ), “greater than or equal to” (>= ), “less than or equal to” (<= ), “not equal to” (<> ) and some others. These operators are used a lot of times within DAX statements to perform filtering operations.
DAX in Power BI
While the language is what it is across the different tools where it is being used, the focus here is Power BI, the Desktop.
DAX helps in 4 areas in Power BI:
1. Creating Tables:
There are sometimes need to add tables to our models on the fly without having to pull from any particular data source. A good example of this is Date Tables, which are important in the model for Time Intelligence computations and Model Efficiency.
DAX helps in creating tables in Power BI
2. Creating New Columns:
Sometimes, we need to analyze by data points that are not part of the source data. We often need to derive new columns from the data. For example, where a data set has fields for Sales and Profit but there is a need to also analyze cost.
It becomes somehow necessary to create a new column/field for cost, that will be a subtraction of the Profit column from the Sales column.
DAX helps in Creating New Columns
3. Creating Measures:
Measures are dynamic formulas we need Power BI for advanced and complex computations. The beauty of DAX itself lies in Measures. A measure is unit of computation that gets evaluated only when it is visualized on charts, cards or table visuals.
DAX helps in creating new measures
4. Defining Roles for data access restrictions for users (Row Level Security):
Row Level Security (RLS) in Power BI is a data access/control feature that allows creation of roles and assigning what part of the data each role can see in their reports. Users are then assigned to those roles. So, we share a single report, but different users see different things from that same report, based on their role definition.
For example, if we want to share a report with users from different regions, we can define 4 different roles for East, West, South and North such that, the users from each region will see their reports based on the data from their regions alone.
Defining Roles for data access restrictions for users
I will share in my next post, a quick introduction of using DAX in the 4 ways listed above on Power BI. And we will continue to build on that every week, learning the core concepts of DAX and soon enough, we’ll start writing some complex formulas and DAX should start becoming easier.
P.S. check out Zainab’s post this week on how to build a calendar table with Power Query.
