In this Power BI Tutorial, we will discuss what is Power BI DAX (Data analysis expressions). Also, We will see how to create and use Power bi DAX calculated table, column, measures.
Also, we will see an example of how to use Power bi DAX calculate filter and function.
SharePoint Online tutorial contents:
Power BI DAX introduction
Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI Desktop, and Power Pivot in Excel.
DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.
Power BI DAX is used basically to perform advanced calculations and queries on data in related tables and columns in tabular data models. DAX helps you create new information from data already in your model in Power BI.
Why we use dax in power bi
We can create various useful reports without using DAX in Power BI. Then the question comes why we use DAX in Power BI or What is Dax used for in Power BI?
If you want to create a report on growth percentage or if you want to create a report based on a date range in Power BI, then in those cases Power BI DAX.
Power BI DAX will be helpful if you want to create these kinds of complex or dynamic insights from your existing data.
We will create and use DAX formulas to get most out of your data.
DAX Data Types
In Power BI Desktop, DAX Function is used to work only with the tables. This function is having two primary data types as Numeric and Non-numeric or Other.
Numeric data types contain decimals, integers, currency, etc. and Non-numeric data types or Other contains binary objects, strings.
In the Power BI DAX function, there is another data type present known as DateTime. This DateTime data type can be used for calculation purposes of any time period. The DateTime is stored as a value with both the decimal and integer parts.
How to use Power bi DAX
Now, we will see how to use Power BI DAX. Also, we will see how to create some basic formulas and use them to create some useful Power BI report.
Well, before creating any formula in DAX, if you have knowledge of excel functions, then it will be easier for you to create DAX functions in Power BI also.
We will see here, how to create various DAX formulas and use them with our data.
Here, we will use a SharePoint list for our reporting in Power BI. You can check out an article on How to Create Power bi report from SharePoint Online list.
Here I am using the below SharePoint list.
We can create various DAX calculation formulas in the below things:
- Calculated columns
- Calculated tables
- Row filters.
Power bi DAX calculated column
By using calculated columns in Power BI, we can add or create a column to an existing table in Power BI. The values will be populated by using a DAX formula.
When a calculated column contains a valid DAX formula, values are calculated for each row as soon as the formula is entered. Values are then stored in the in-memory data model.
In Power BI DAX, if you are creating a new measure or column, you should understand the format of table names. The table name should be in the below format:
Here the single quotes of the table name are compulsory if any spaces are present in the table name. If there is no space in the table name, then the single quotes are not necessary to put as like the below syntax:
When you are using the Power BI DAX, Follow these below two things for best practices.
- Don’t put any space in the table names
- Always make sure to add the table name in formulas
We can create a calculated column from the Modeling tab like below:
Also, you can go to the Data tab, then from Table tools, click on the New column to create a calculated column in Power BI.
In the list I have two columns as Price and Quantity, we create a calculated column to get the Total Price like below:
Total Price = 'Course Sales'[Price]*'Course Sales'[Quantity]
And you can also see the column in the Power BI data like below:
Similarly, I have created another calculated column based on price. Here I have used an If in Power BI DAX to create a Course Type column:
Course Type = If('Course Sales'[Price]>0,"Paid","Free")
If the price is greater than zero, then the course type if Paid course else it is a Free course like below:
Power BI Calculated Measure
In Power BI, If you are calculating any ratios or percentages, then you can use this Calculated measure.
Measures are dynamic calculation formulas where the results change depending on context. The result will not appear immediately because it require a context, it will display in tooltip how the result will be.
In Power BI Desktop, measures are created and displayed in Report View or Data View. Measures you create yourself appear in the Fields list with a calculator icon. You can name measures whatever you want and add them to a new or existing visualization just like any other field.
There are three approaches to create a new measure using a DAX formula as:
Create a new measure by using the Modeling tab
To create a new measure in Power BI Desktop, Select the New Measure button from the Modeling tab.
It will be better if you will show the visual in the Data view of Power BI Desktop. It will make you easy to write the DAX formula in the Formula bar which you can see in the below screenshot.
After clicking on the New Measure button, You can see there will be a new measure icon appear in the Fields pane as shown below.
Also, you can see the new measure formula bar with the new measure name. (In the formula bar you can write your Power BI DAX formula as per your requirement)
Create a new measure by using the Home tab
We can also create a new measure from the Home tab. In the Power BI Desktop, Click on the Home tab and select the New Measure option as shown below.
Create a new measure by right-clicking on the Table
Similarly, you can create a new measure by using the right click on the table name. In Power BI Desktop, Go to the Fields pane, select your table, and right-click on it. Then select the New measure option as below screenshot.
Now we will see how to create a Power BI measure which will provide the SUM of Total Sales.
You can write the formula like below:
Total Sales = SUM('Course Sales'[Total Price]) Or Total Sales =SUMX('Course Sales','Course Sales'[Total Price])
We can see the measure in the field list.
Similarly, we can create power bi calculated measure with filter.
In the below measure, we are displaying the total sales of “SharePoint Development Training” course.
Total SharePoint Development Sales = SUMX(FILTER('Course Sales','Course Sales'[Course]="SharePoint Development Training"),[Total Price])
Similarly, I have created another measure to display total amount of sales for the Course Bundle.
Total Course Bundle Sales = SUMX(FILTER('Course Sales','Course Sales'[Course]="Course Bundle"),[Total Price])
And, I can use both the measures in a Power BI visualization.
Power bi DAX calculated table
In Power BI, most of the time, you create tables by importing data into your model from an external data source.
But Power BI DAX calculated tables, let you add new tables based on data you’ve already loaded into the model. Instead of querying and loading values into your new table’s columns from a data source, you create a Data Analysis Expressions (DAX) formula to define the table’s values.
Created a calculated table using the DAX formulas or query.
Calculated tables support relationships with other tables. The columns in your calculated table have data types, formatting.
In Power BI desktop, Go to the Modelling tab and then Create New Table.
Then you can add the formula like below:
SharePoint Development = FILTER('Course Sales','Course Sales'[Course]="SharePoint Development Training")
The table with formula looks like below:
I have created another table by using the below DAX formula.
SPFx Development = FILTER('Course Sales','Course Sales'[Course]="SPFx Training")
Power bi DAX union two tables
We can also use the UNION DAX function to create a union of two tables like below:
Dev and SPFx = UNION('SharePoint Development','SPFx Development')
The final table now looks like below:
Power BI DAX Row Filter
A DAX formula must evaluate to a Boolean TRUE/FALSE condition, defining which rows can be returned by the results of a query.
Below is the formula we can write in a measure which will display the total revenue of country India.
India Revenue = CALCULATE([Total Sales],'Course Sales'[DispName]="India")
We can also create a table by using a Power BI row filter:
Table = FILTER('Course Sales','Course Sales'[Price]>150)
Power BI DAX Functions
To use Power BI DAX Function, the most important thing you have to learn is the formula or syntax of the language.
The below screenshot represents an example of a DAX Syntax. By using this example, you can understand properly that how you can use the DAX syntax.
Here I have explained each element of the syntax below:
- 1: It represents the name of the new measure (IncomeTaxRates2019).
- 2: It is the equal (=) operator which represents the beginning of the DAX syntax and also equating the two sides.
- 3: This SUM is the DAX function which is used to add the values of a given field (NetWageEarningsAfterTax) from the table (IncomeTaxRates).
- 4: It represents the name of the table (IncomeTaxRates) from which the column is taken in the formula.
- 5: It represents the name of the column (NetWageEarningsAfterTax) from which the formula will use the values.
- 6: This represents the multiplication (*) operator which will help to multiply two elements.
The DAX syntax is used to calculate the product of the sum of the values in NetWageEarningsAfterTax and 5.05.
After all the calculation of DAX syntax, the value will store in a new measure known as IncomeTaxRates2019.
Type of Power BI DAX Functions
In Power BI, there are some pre-defined DAX functions present which will help you to analyze your data. Every function has an argument that performs a particular operation.
The arguments in sense of it can be a number, text, column reference, logical value as TRUE or FALSE, etc. You can use one or more than one argument in a DAX formula.
In the Power BI Desktop Formula Bar, When you will start typing the DAX syntax, it will help you to show all the available functions which you want to select. You can select any function by using up and down arrow keys on your keyboard.
Let’s take an example. Suppose if you want to add a function name as SUM. So if you will type S on the formula bar, then all the functions will appear where the first letter starts on S.
In Power BI DAX, there are different types of functions present such as:
- Aggregation functions: Power BI DAX has aggregation functions which are basically used only for numeric columns.
There is a special aggregation function that ends in X as like SUMX that performs on multiple columns.
Some of the Aggregate functions are SUM, SUMX (and other X functions), MIN, MAX, AVERAGE, etc.
- Counting functions: This Counting function will help you to count some distinct values, table rows, non-empty values, etc.
Some of the counting functions are COUNT, COUNTA, COUNTBLANK, COUNTROWS, DISTINCT COUNT.
- Logical functions: Basically Logical functions are used as an operator. Suppose, if you want to perform AND operator or OR operator, then instead of writing, you can simply use && or || in the DAX formula.
Some of the Logical functions are: AND, OR, NOT, IF, IFERROR, etc.
- Information functions: In Power BI DAX, these Information functions are used situationally.
It performs on the given condition is a function for the value given and it returns as TRUE or FALSE. For instance, the function ISERROR will return TRUE if the value evaluated contains an error.
Below represents some of the Information functions as:
- USERNAME, etc.
- Text functions: Power BI Text functions are very much similar to the Excel string functions. It helps to evaluate the string values.
The Power BI Text functions are:
- SUBSTITUTE, etc.
- Date and Time functions: This function is the most important function in the Power BI DAX. It helps to calculate the particular date and time values and retrieve the exact date from the date values.
Always remember, the Date and Time function values are DateTime data type.
Some of the Date functions are:
- CALENDAR, etc.
Also, you may like the below Power BI articles:
- Power BI Custom Visuals
- Add Title, Image, and Video to Power BI Dashboard
- PowerBI Report Filter using Slicer Visualization
- Access to the resource is forbidden error in Power BI
- Create a Power BI report from Excel using Power BI Desktop
- Add Web Content to Power BI Dashboard
- [Video Tutorial] Create Power BI Dashboard step by step tutorial
- Microsoft Power BI Error: This content isn’t available
- Power BI On-premises Data Gateway
- Power BI Change Data Type of a Column
In this tutorial, we learned what is Power BI DAX and
- Why we use DAX in power bi
- DAX Data Types
- How to use Power bi DAX
- Power bi DAX calculated column
- Power BI Calculated Measure
- Power bi DAX calculated table
- Power bi DAX union two tables
- Power BI DAX Row Filter
- Power BI DAX Functions
- Type of Power BI DAX Functions
Hello Everyone!! I am Bhawana a SharePoint MVP and having about 10+ years of SharePoint experience as well as in .Net technologies. I have worked in all the versions of SharePoint from wss to Office 365. I have good exposure in Customization and Migration using Nintex, Metalogix tools. Now exploring more in SharePoint 2016 🙂 Hope here I can contribute and share my knowledge to the fullest. As I believe “There is no wealth like knowledge and no poverty like ignorance”