Creating measures using DAX

DAX (Data Analysis Expressions) Measures in Power BI are dynamic calculations defined using the DAX formula language. These measures can perform standard aggregation functions or more complex custom calculations depending on the context in which they are used. DAX allows us to calculate, combine and filter datanwhich helps in returning one or more values based on user-defined formulas.

Syntax of DAX Measures:

MeasureName = FunctionName(TableName[ColumnName])

Alternatively for more complex expressions:

MeasureName = FunctionName(TableName[Column1], Expression, TableName[Column2])

Parameters:

• MeasureName: Name of the DAX measure we're creating.
• '=': Shows the start of the formula.
• FunctionName: The DAX function you are using (e.g., SUM, MAX, etc.).
• Parentheses ( ): Enclose the arguments or columns used in the function.
• Table and Column References: Specify the table and column being used in the function, along with any scalar operators like +, -, *, /

To create and use DAX measures in Power BI, we should have a basic working knowledge of Power BI Desktop. Familiarity with Excel formulas can be helpful but understanding how formulas are written is important like:

1. Maximum Forecast By Country:

Maximum Forecast By Country = MAX(Forecast[Forecast])

2. Profit on Sales:

ProfitOnSales = SUM('Order Data'[Revenue]) - SUM('Order Data'[Cost])

Understanding Measure

A measure is a dynamic calculation performed on data fields that can include arithmetic operations or aggregation functions. In Power BI, measures are created using DAX functions like SUM, AVERAGE, MAX, MIN and COUNT. It help in summarizing data and generating insights based on user-defined criteria.

Examples of Measures: Total Sales, Total Salaries, Average Quantity, Average Order Value, Count of Customers, Year-to-Date (YTD) Sales, etc.

1. Creating Measure Table

A Measure Table in Power BI is a special table used to store all the DAX measures created in our report. It helps organize measures for better management and clarity in our data model. To create a new Measure Table in Power BI:

• In Model View click the "Create New Table" button from the menu bar.
• Provide a name for the table.
• Click the "Load" button to create the new table.

2. Create New Measure from Measure Table

We can easily create new measures and add them to our Measure Table in Power BI. To create a new measure:

• Right-click on the Measure Table in the Fields pane.
• Select "New Measure" from the context menu.
• In the formula bar, enter the DAX formula for our new measure.

3. How to create a DAX Measure in Power BI?

1. Open Power BI Desktop

2. Import Data from a Data Source like Excel, SQL Server, Access, Oracle, MySQL or any other data set like XML, JSON, Text File

3. Once the data source is loaded, the Fields list will be added to the right of the window.

4. Create a New DAX Measure by clicking the 'New Measures' button at the tool bar at the top.

5. Input New Measure Name and default will be 'Measure' and add '=' sign

6. Add Function Name like Sum, Average, MAX, MIN or any other calculation function and open a bracket '('.

7. Select the Column name from the list of columns after typing in the table name and close the bracket ')'

8. The new measure is ready. The new measure will be displayed with the fields list.

9. Use the Measure to create Visualization in Power BI as below.

Creating Measures Using AggregateX Functions

AggregateX functions are used to perform row-wise calculations over a table or table expression. These functions aggregate values based on expressions evaluated for each row. Theses are some of the commonly used AggregateX function: SUMX, AVERAGEX, COUNTX, MINX and MAXX

Syntax:

AggregateX(Table, Expression)

• Table: Table or table expression to evaluate.
• Expression: Calculation or expression to calculate for each row of the table.

Example:

AggregateX Demo = SUMX('Global-Superstore','Global-Superstore'[Shipping Cost] - 'Global-Superstore'[Sales])

Common Aggregate DAX Functions

AggregateX Functions as Iterator Functions

AggregateX functions like SUMX, AVERAGEX, COUNTX, MINX and MAXX are known as iterator functions. These functions are useful when we need to perform calculations across rows of a table involving columns from different related tables. An iterator function calculates the given expression for each row of the table specified. This allows us to perform row-wise operations and aggregate the results.

Example: Consider an example where we want to calculate the sum of cookies shipped but only for values above 800:

SumOfCookiesabove800 =
SUMX(
'Order Data',
IF('Order Data'[Cookies Shipped] > 800,'Order Data'[Cookies Shipped],0))

In this example, SUMX iterates over each row in the Order Data table and for each row, it checks if the value of Cookies Shipped is greater than 800. If it is, it adds the value to the total sum otherwise it adds 0. This allows us to sum only the rows that meet the specified condition.

Creating Calculated Columns Using DAX

Calculated columns are new columns created in an existing data table using a DAX formula. Once created these columns become part of the table and can be used in Power BI visualizations like any other field. To create a new calculated column in Power BI:

• Select the table where you want to add the calculated column.
• Click the "New Column" button in the toolbar.
• In the DAX formula bar enter the DAX formula to define the calculation for the new column.

DAX CALCULATE and VALUES Functions

1. CALCULATE Function

The CALCULATE function in Power BI is one of the most fundamental DAX functions. It is used to find an expression with the application of filters or conditions which allows us to change the context in which the calculation is performed.

Syntax:

CALCULATE(Expression, [Filter1], [Filter2], ...)

• Expression: Calculation or measure we want to find.
• Filter1, Filter2, ...: Optional filter conditions that modify the context of the calculation.

Example: To calculate the total sales for a specific country, we can use the CALCULATE function:

SalesUSA = CALCULATE(SUM('Sales'[Revenue]), 'Sales'[Country] = "USA")

This formula calculates the sum of the Revenue column but only for the rows where the Country is "USA".

2. VALUES Function

The VALUES function in DAX is used to retrieve distinct values from a column or table. This can be useful when we need to work with unique values for calculations.

Syntax:

VALUES(TableORColumnName)

Example: To count the number of distinct cities:

Number of Cities = COUNTROWS(VALUES(City[CityName]))

Using the FILTER Function in DAX

The FILTER function in DAX is used to return a filtered table based on specified conditions. It is used in combination with other functions like CALCUL

Syntax:

FILTER(Table, FilterCondition)

Example: To calculate the sum of Forecast for the countries "USA" and "Italy":

FilterDAX1 = Calculate(Sum(Forecast[Forecast]),FILTER(Forecast, Forecast[Country]="USA" || Forecast[Country]="Italy"))

Variable for Debugging

Variables in DAX are used to store intermediate values and reuse them within the same formula. Using variables can make our formulas more readable and maintainable.

Below is the Syntax of how a variable is used:

VAR variable_name = expression
RETURN result_expression

• VAR: Declares a variable.
• RETURN: Defines the expression that will return the result.

Example: Calculating the discount offered based on the quantity of cookies shipped:

DiscountOffered = VAR SaleQuantity = SUM('Order Data'[Cookies Shipped])
VAR CostOfCookies=SUM('Order Data'[Cost])
Return IF(SaleQuantity > 800, CostOfCookies * 10/100, SaleQuantity * 5 / 100)

In this example, two variables are created: SaleQuantity and CostOfCookies. The discount is calculated based on the number of cookies shipped which helps in applying different rates for quantities above or below 800.

Output:

DAX Studio

DAX Studio is a tool to write, execute and analyze DAX queries in Power BI. It provides a range of features that help developers create, test and optimize DAX formulas. It integrates seamlessly with Power BI which allows direct connections to Power BI models and datasets.

With DAX Studio, we can:

• Write complex DAX queries in the Query Editor.
• Execute the queries and view results immediately.
• Optimize your DAX formulas for better performance.

Writing Queries in DAX Studio

To write a query or calculation in DAX Studio, the query typically starts with the EVALUATE keyword as shown in the example below:

EVALUATE
SUM('Order Data'[Cost])

This query calculates the total cost from the Order Data table.

Example of Using DAX Studio Editor:

We can also use DAX Studio to select all rows from a table and view them directly in the Result View window which displays the output at the bottom of the screen. With DAX measures and tools like DAX Studio, we'll be able to create more and interactive reports to gain valuable insights from our data.