DAX stands for Data Analysis Expressions a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Also, It’s a formula language associated with the Data Model of Microsoft Excel Power Pivot and with Microsoft Power BI.
There’s a definition called DAX function which is a built-in function presented from DAX language to enable you to perform various actions on the data in the tables in your Data Model.
We’ll focus on understanding DAX formulas used in calculations, more specifically, in measures and calculated columns. You should already be familiar with using Power BI Desktop to import data and add fields to a report, and you should also be familiar with the fundamental concepts of Measures and Calculated Columns.
Before creating your own formulas, let’s take a look at DAX formula syntax. Syntax includes the various elements that make up a formula, or more simply, how the formula is written. For example, here’s a simple DAX formula for a measure:
- The measure name in this example is Total Profit
- Equal Operators indicate the beginning of a formula
- The DAX Function in our example is SUMÂ which adds all values in column Profit
- DAX function receives it’s input parameters, DAX function can have one or more input parameters, in our example, it will receive the column on which we need to get values summation
- Inside SUM DAX function we passed the table name and column name as required by this function, different functions will have different required input parameters
DAX functions
We can categorize DAX functions into different categories as following, and we placed a sample functions from each category
Aggregations
Counting
Logical
Text
Date
Information
So let’s describe the concept of our first Measure then describe the Calculated columns .
How to create a measure formula
Firstly let’s explore our Table, It’s for Orders with eight columns (order ID – Customer ID – Product ID – Category – Sales – Quantity – profit – shipping cost)
Let’s start our first measure
As we mentioned before DAX is a functional language, which means the fully executed code is contained inside a function that built before.
Firstly you should know the general format of our tables at DAX:
'Table Name'[ColumnName]Adding the single quote to the table name if there are spaces in the table name, and the single quotes can be omitted if there’re not existing.
Use Case
let’s imagine that your manager wants to know the numbers of customers that ordered from his store, so you have a column name called (Customer_ID) you can count the number of them, but if the same customer ordered more than one order say 2 orders so, in this case, he’ll have the same Customer ID with multiple Order IDs and each orderID have its several information about the order, so to count the whole number of customers use COUNT function, and to count the whole number without the redundant values use DISTINCTCOUNT.
from toolbar select Home –> New measure.
After select New measure, type the name of the measure you want to add, and here in this case we want to know the “Total Number of Customers” so we write the name then put equal sign then write the name of your function or select it during you’re writing.
select/write the name of the function DISTINCITCOUNT.
After writing the function, you can write between the 2 brackets the table name and the column you want to calculate their values.
so, after this step you’ll find the measure add to the table, let’s present it by choosing the card chart.
Congratulations! you created your first measure using DAX This is the way to add any measure with any DAX expressions we mentioned or any other DAX expressions.
So now you learn about measures, you can use any other measure like SUM of the total profits and so on. Now let’s try the calculated columns.
Another function is MAX and as we mentioned before it refers to a MAX of column values.
so imagine that you want to know the average of your shipping cost so let’s do a new measure with the name “Max shipping” and use the function MAX to calculate this field.
Now after using the function you can get a card char and add you data on it.
Done!
How to create a Calculated column
From our data we can extract another insight, let’s say you want to get a column to have the Category with Upper letters, so let’s begin, from data icon then Home select New column and write your new column name let’s say ” Category with Upper ”
As the previous example at measure just use the function UPPER and select the column you want from the table.
Now you have another new column has your data in upper letters.
Let’s take another example
Imagine that you want a column to show your total profit not just the profit by each item, so you need to multiply each profit with the quantity of the order. So as the steps of the previous example select New column then write your column name like ” Total profit ” for example, so choose the first column of the profits, write the multiply sign ” * ” , write the second column which is quantity then press Enter.
conclusion
Data Analysis Expressions (DAX) is a library that has functions and operators that can be combined to build formulas and expressions to use in Power BI, Analysis Services, and also Power Pivot in Excel data models.
