Introduction to DAX and Basic Formulas

Prerequisite

As we are using the DAX (Data Analysis Expressions) language, we will need Power BI or Power Pivot in Excel for implementing it.

Assuming you are using Power BI Desktop, you can download it from here.

Also, assuming that you already have a dataset prepared, let's begin our journey to learn more about DAX!

Dataset Setup in Power BI

1. Open Power BI Desktop.

2. Click on the Home tab and select Get Data.

3. If your data is in a CSV format, select Text/CSV or choose the appropriate one (Excel, SQL Server, etc).

4. Click on Connect, browse to your file, and click on Open.

5. Explore the Preview Data window and if everything looks fine, click on Load.

The data is now loaded in Power BI and ready to be used for creating DAX formulas.

Introduction to DAX

DAX is a collection of functions, operators, and constants, which can be used in a formula or expression, to calculate and return one or more values. These expressions define a set of values, but do not specify an order, or hierarchy, on those values.

When we create DAX formulas, it allows us to use it in:

• Calculated Columns: These are the columns that we add to the existing tables.
• Measures: These calculations are used in the context of the visual in the report.

Basic DAX Formulas

Below, we'll implement a few basic DAX formulas to get you started.

SUM

This DAX formula returns the sum of an entire column.

Syntax:

SUM()

Example:

Total Sales = SUM(Sales[Sales Amount])

In the above query, Sales is the table name, and Sales Amount is the column name. This will return the total sales amount.

AVERAGE

This DAX formula returns the average of a column.

Syntax:

AVERAGE()

Example:

Average Sales = AVERAGE(Sales[Sales Amount])

COUNT

Counts the number of rows in the target column.

Syntax:

COUNT()

Example:

Sales Count = COUNT(Sales[Sales ID])

Make sure to replace Sales and Sales ID with your table name and column name.

These basic DAX formulas should be enough for this part of the project to give you an introduction and help you understand the basic calculations in DAX. And remember, you will need to create a new Measure and use the respective DAX formula in the Formula section to use these in the Power BI reports.

Let's get down to implementing more complex scenarios in the next steps of the project!

Understanding and Manipulating CALCULATE() function

Exploring CALCULATE() Function

The CALCULATE() function in DAX is a crucial command to create complex business solutions requiring changes in the calculation context i.e., to manipulate the context in a calculation.

Let's jump straight into understanding this function using a simple sales dataset.

Dataset

Assuming you have a dataset named "Sales" with the following data:

Understanding CALCULATE()

The syntax for CALCULATE() is as follows:

CALCULATE ( , , ....)

• <expression> is any DAX expression that returns a single scalar value.
• <filter1>, <filter2>.. are optional and represent expressions that define the filters.

Let's calculate the total sales amount.

Total Sales = SUM ( Sales[SalesAmount] )

We can further manipulate the above measure using CALCULATE(). Let's calculate the sales amount for product "P1" only.

Sales P1 = CALCULATE ( [Total Sales], Sales[Product] = "P1" )

When you put this measure in a visual, it returns the total sales amount for P1 only, regardless of any other context being applied.

Manipulating CALCULATE() Function

The power of CALCULATE() lies in how it can change the context of a calculation. You can add, replace, or remove contexts by applying the relevant filters in CALCULATE() expressions.

Adding Context

Adding context means adding more filters to the existing context. Let's say, you want to calculate sales for product "P1" for the month of "Jan".

Sales P1 Jan = CALCULATE ( [Total Sales], Sales[Product] = "P1", Sales[Month] = "Jan" )

Replacing Context

Replacing context means changing one or more aspects of the existing context. If you want to calculate sales of any product, but specifically for the month of "Feb", you would do:

Sales Feb = CALCULATE ( [Total Sales], Sales[Month] = "Feb" )

Removing Context

The ALL() function can be used within CALCULATE() to ignore the context. For example, to calculate the total sales regardless of product or month:

Total Sales Ignore Context = CALCULATE ( [Total Sales], ALL ( Sales ) )

Remember, CALCULATE() is the only function that can change the context in which the data is evaluated. Understanding and appropriately applying this function is vital to mastering DAX and effectively analyzing your data.

Important: CALCULATE() changes context in an existing row context; it cannot create a row context on its own. This is essential to remember when you deal with complex expressions and scenarios.

Have fun exploring the other DAX functions!

Implementation of Advanced Use of ALLEXCEPT() function

In this implementation, we're going to delve into particular ways the ALLEXCEPT() function can be utilized in Power BI using DAX on a sales dataset.

Section 1: Overview of ALLEXCEPT()

ALLEXCEPT() is a DAX function that filters all columns in a table except for the ones defined in the function call.

General syntax:

ALLEXCEPT (, , [], ...)
Section 2: Working with ALLEXCEPT()
Let's say we have a sales dataset named 'SalesData' as below:

OrderID
Product
Category
Region
Sales
Quantity


1
A
Food
West
100
2


2
B
Drink
East
200
3


3
C
Food
North
150
4


4
D
Drink
South
300
5

TotalSalesByCategory = CALCULATE(
                              SUM(SalesData[Sales]),
                              ALLEXCEPT(SalesData, SalesData[Category])
                           )

AvgQuantityByRegion = CALCULATE(
                             AVERAGE(SalesData[Quantity]),
                             ALLEXCEPT(SalesData, SalesData[Region])
                          )

SalesPerProductPerCategory = CALCULATE(
                                   SUM(SalesData[Sales]),
                                   ALLEXCEPT(SalesData, SalesData[Product], SalesData[Category])
                               )