Power BI Dax

What is DAX in Power BI?

DAX (Data Analysis Expressions) is a language used in Power BI tools and other Microsoft applications, such as Power Pivot in Excel. Power BI DAX expressions are used to create calculated tables, calculated columns, and measures in Power BI, used for data analysis and visualization. DAX formulas include functions, operators, and values and are used for complex calculations and data queries in related columns and tables in tabular data models.

For instance, a calculated column refers to adding a new column to an existing table or data model by creating a DAX formula where you can define or map the column’s values. A calculated table creates a new table using the data already loaded into Power BI and using the DAX formula to define the table’s values. Measures represent dynamic calculation formulas used in data reporting and analysis. It allows you to create data aggregation results such as SUM, AVERAGE, MIN, MAX, and COUNT, which can be further used in data analysis.

Following is an example of the Power BI DAX SUMX function to create a calculated column SUMXSales in a table.

How to Upload Data to Power Bi?

You can upload data to Power BI in multiple ways.

Option 1: When you open the Power BI tool, you will be navigated to the Home tab. Under the Home tab, you will see the multiple data upload options as highlighted below.

Option 2: Choose from the data sources in the Data group under the Home tab.

To review the complete list of data sources that Power BI support, click on the Get data dropdown followed by the More.. option. You can choose any available options for uploading the data to Power BI.

Let us understand this using an example. First, we will upload a Financial sample file(Excel Workbook) in Power BI using the following steps below:

Step 1: Click on the Get data dropdown under the Home tab.

Step 2: Select the Excel Workbook option.

Step 3: Navigate to the file location and select the appropriate file.

Step 4: Power BI will open a Navigator screen with a display option. Select the correct file to preview the dataset.

Step 5: Click on the Transform Data button. The dataset will open in a new window i.e., the Power Query Editor tool window.

Step 6: Format the dataset. Here, we have formatted the Country and Segment columns to UPPERCASE and changed the data type of “Units Sold” and “Gross Sales” to Whole Numbers.

Step 7: Navigate to the Home tab and choose the Close & Apply option.

The system will save the changes and close the Power Query Excel Editor window. Finally, power BI will load the updated dataset, and you can see all the columns in Model View.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to do DAX Calculation in Power BI?

To perform the Power BI DAX calculation, follow the steps mentioned below.

Step 1: Open Power BI Desktop.

Step 2: Import the dataset to Power BI and apply data transformation (if applicable).

Step 3: Navigate to the Modelling tab.

Step 4: Now choose one of the options i.e., New measure, Quick measure, New column, or New table available in the Calculations group.

Let us understand each of these options through some examples below.

New Column or Calculated Column

The column Profit Percentage is a calculated column representing the Profit as a percentage of the Sales.

This column is created using a DAX formula in Power BI with the steps highlighted below:

Step 1: Go to the Modelling tab.

Step 2: Select the New column option in the Calculations group.

Step 3:

• Define the new column.
• Specify the DAX formula for populating the values.
• Click on the Commit button to save the details.

Step 4: Once the column is created, it will be visible in the Model view.

Click on the Data view to see the values of this calculated column for each record.

Calculated Table or New Table

The table financials country is a calculated table that stores a unique list of countries from the entire dataset. This table is created using the New table feature in Power BI using the steps highlighted below:

Step 1: Go to the Modelling tab.

Step 2: Select the New table option in the Calculations group.

Step 3: Define the new table, specify the Power BI DAX formula for populating the values, and click the Commit button to save the details.

Here we are creating a table of a unique list of countries listed in the dataset.

Step 4: Once the table is created, it will be visible in the Model view.

The calculated table will also be visible in the Data view.

Measures or New Measure

Average Sales is a measure that represents the average of all the sales that occurred across the years. This measure is created using the New measure feature in Power BI using the steps highlighted below:

The steps used to create a new measure are as follows:

Step 1: Go to the Modelling tab.

Step 2: Select the New measure option in the Calculations group.

Step 3: Define the new measure using the DAX formula and save using the Commit button.

Step 4: Once the table is created, it will be visible in the Model view.

Step 5: Drag the new measure into the chart to create an interactive dashboard in Power BI

Note: There is also an option for creating quick measures. Using this option, you can choose from a predefined list of measures from the Calculation drop-down.

Functions in DAX Power BI

Functions in Power BI DAX can help you create complex and advanced formulas to further use for performing calculations in Power BI. Functions accept arguments such as text value, numbers, date, references, or other functions, etc., in a specific order or format. The different categories of functions include:

• Aggregate Functions
  • These functions perform aggregations or calculations across all the records for a column. They include COUNT, SUM, SUMX, AVERAGE, MIN, and MAX.
• Date and time functions
  • These functions calculate the date and time values, such as the difference between two dates, etc. They include CALENDAR, DATE function, DATEDIFF, WEEKDAY, and YEAR.

• Filter functions
  • These functions perform look-up values in related tables, return specific data types and then filter the values. They include: FILTER, CALCULATE, INDEX, LOOKUPVALUE, and SELECTEDVALUE.
  • For example, Power BI DAX Lookupvalue (LOOKUPVALUE) retrieves a single value from a table that matches a specific set of criteria.

• Financial functions
  • These functions are used explicitly for financial calculations such as accrued interest on security, the future value of an investment, Yield on a bond, etc. They include ACCRINT, FV, PMT, XIRR, and YIELD.

• Logical functions
  • These functions evaluate a logical expression and allow you to create conditional results. They include AND, IF, NOT, OR, and SWITCH excel function.
  • For example, Power BI DAX Switch (SWITCH) is used to evaluate a list of conditions and return a corresponding value for the first true condition.

• Math and Trig functions
  • These functions are used to perform mathematical and trigonometric calculations, such as finding the absolute value of a number, log value or exponential value, etc. They include ABS, DIVIDE, EVEN, EXP, and LOG.

• Time-Intelligent functions
  • These functions assist you in performing data manipulation operations using periods and help build and compare datasets over these periods. They include CLOSINGBALANCEMONTH, DATEADD, FIRSTDATE, LASTDATE, and PREVIOUSYEAR

• Text functions
  • These functions perform string or text-based calculations such as string concatenation, text length, converting the letters into uppercase, etc. They include CONCATENATE, EXACT, FIND, LEN, and UPPER.

• Statistical functions
  • These functions perform statistical distribution and probability-related calculations such as Median, Normal Distribution, Geometric Mean, Standard Deviation, etc. They include GEOMEAN, MEDIAN, NORM.DIST, STDEV.P, and VAR.P.

• Parent and Child functions
  • These functions can assist in managing data set up as a parent-child hierarchy in a data model. They include PATH, PATHCONTAINS, PATHITEM, PATHITEMREVERSE, and PATHLENGTH

• Other functions
  • These functions do not cover any of the standard category functions and perform very unique operations. They include BLANK, ERROR, TOCSV, and TOJSON.

• New functions
  • These functions are newly inducted in Power BI and may not be available in earlier versions of Power BI Desktop. They include LINEST, LINESTX, INDEX, OFFSET, and PARTITIONBY.

DAX functions are similar to the ones used in Microsoft Excel. However, they differ from Excel in many ways, as mentioned below

• Unlike Excel ones, DAX functions don’t refer to a single cell or column reference in a column. Instead, they refer to an entire column or a table to perform data operations or look-ups
• By default, the DAX functions perform operations across all the records or rows in a table. However, single-row-specific functions can be used to restrict the operation to individual rows.
• DAX has specific functions that enable you to perform dynamic calculations based on date ranges.

Important Things to Note

• DAX is case-sensitive, so be careful when typing in functions and calculations.
• Understand data modeling, DAX, and other technical concepts to ensure accurate and efficient calculations.
• Refresh your data regularly to keep your visualizations up to date.
• You can collaborate and share the reports and dashboards within the organization and with external customers in Power BI.

Frequently Asked Questions (FAQs)

Recommended Articles

Guide to Power BI DAX. Here we explain how to upload data file and do DAX calculation in Power BI with examples. You can learn more from the following articles –