COMPLETE Tutorial: DAX Language

The Solution to the Real Life Challenge

January 16, 2020
7:00 pm
Power BI

Leonardo Karpinski

Master of Power BI, creator of the Express Power BI Course and the Complete Course of
Power BI. Trained more than 16 thousand students in recent years and participated in projects in
large national and multinational companies.

Hey guys, how's it going? Today's post is about Master’s Live #1!!

I will create a complete tutorial on the DAX language and a challenge at the end using everything.
that I demonstrated in the tutorial. Moreover, the challenge is based on a real problem that
one of my clients had.

So, stay tuned until the end to learn about:

Introduction to DAX
Difference between implicit and explicit measures
Evaluation contexts
CALCULATE function
ALL function
ALLSELECTED Function
VALUES function
Challenge to calculate Average Chorn
Introduction DAX
I always like to reinforce with you the 4 phases within the BI process:

ETL → Extract (extrair), Transform (transformar) e Load (carga)

Modeling
Visualization
Sharing

The DAX language is embedded within the modeling phase of the process.
Normally in ETL we use the M language that is specific to Power Query.
the term 'drag and drop' refers to
The assembly of visuals and sharing is mainly related to Power.
BI Online/Service.

So, we use the DAX language to create all of our project calculations. DAX
it is an acronym created for Data Analysis Expressions and has many expressions that
remembers those from Excel, but all formulas are in English.

Ahhh but you must be thinking "Where can I use this DAX thing!?"
There are 3 situations where DAX appears:

Calculated columns
Measurement calculation
Tables

Well, cool!! But you might also want to know what the greatest power of DAX is? The
The greatest power of DAX is to create measures, but I will show examples of all.

Columns
Let's do an example to create a calculated column:

Figure 1: Creating Column with DAX

Stages:
In Data, select the table fVendas → In 'Column Tools'
click on 'New column'
DAX Formula:
Valor Venda $
Sales[Sale Value] /4.2

What happens when we do this? We just increased our model leaving the
larger file, affecting processing (decreasing performance). To exemplify
another column problem, let's analyze a new column created:

Figure 2: Creation of Total Sales Column

Stages:
In 'Data', select the dProduto table → In 'Column tools'
click on 'New column' → Select the measure [Total Sales]
DAX Formula:
Total Sales
SUM ( fVendas[Sales Value])

With these values, we will create a column chart segmented by date.

on the page:
Figure 3: Creation of Graphs for Analysis

Stages:
Clustered column chart: Axis → Product, Values → Value
Sales (sum)
Data segmentation: Field → Year

When we change the data segmentation range, see what happens to the
sales values:

Figure 4: Change in data segmentation

Nothing! Exactly that... nothing! This happens because the created column is not influenced.
for the change of the date context (they are static). In other words, we have one more point of
attention here.

The third problem, which requires a lot of attention, is with the level of aggregation of the
columns. Some examples of formulas where this problem can occur are: counting
different, average, percentages.

In general, the measures are used in the fields of segmentation.

visuals.

Measures
There are 2 types of measurements: implicit and explicit. The implicit works when I
I take a column of values (numerical) and use it in some visual. Automatically the
Power BI makes a calculation (figure below with examples) with this field when it goes to
the visual.
Figure 5: Cards with the Product ID field

Stages:
In "Views" click on "Card" → For all cards the
put 'Product ID' in 'Fields'
Figure 6: Types of accounts to display in the visual

The explicit measures are calculations made based on formulas created in DAX! It is a
great practice (yes, better than good... hehe) to create explicit measures for values
that will be used in the visuals.

Let's exemplify a measure with the revenue values:

Figure 7: Ways to create measures

Figure 8: Editing area and formula construction

Stages:
1. Right-click on the table or folder where the measurement is
Click on 'New measure'
you
2. With the table or folder where it will be created selected, click on
Home Page
DAX Formula:
Revenue
SUM ( fVendas[Sales Value])

Some advantages of using explicit measures are:

Performance
Nomenclature
Referencing the measure in other measures of the report

Advantages of explicit measures in relation to the columns:

Performance: the measures are only processed (CPU usage) at the moment
for the desired contexts and use
They are dynamic
Does not consume memory

Evaluation contexts
In Power BI, the measures are dynamic (as I mentioned earlier). Comparing with the
Excel, here you don't need to use the SUMIF function and specify any arguments. The
information provided in lines/columns/axes/segmentation will form a context
that the measure is respected. Believe me, it's not difficult... but it requires practice.

Shall we do an example? Imagine that you want to analyze the revenue figures.
by product subcategory. In Power BI, we do not need to specify in a formula
this, the measure itself will respect the context of the visual:
Figure 9: Support table (contexts for measurement)

Stages:
In 'Views' select 'Table' → Assign the fields accordingly
image

Do you remember how revenue is calculated? No? Let's look at the formula again.
of billing:

Revenue
SUM ( fSales[Sales Value])

Note that at no time did we specify anything about subcategory. However, the measure
respect this context within the visual.

Let’s set up another example to reinforce:

Figure 10: Support Table 2 (contexts for measures)

Stages:
In 'Views' select 'Table' → Assign the fields accordingly
image

And in this case, how many assessment contexts do we have? Here we have 1 filter, but we have 4.
evaluation contexts!!! The 4 are: France, Germany, United Kingdom, and Total.

Legal, right?!! I know you and you must be wondering... 'What is Power BI doing'
doing?” Well, there’s nothing better than an example. Let’s analyze France.
separately. France is a value in the Country column in the Client table. So for the
context of France as if a filter were applied to the column of this table.

To be clear, we will apply this filter to our table in data:

Figure 11: Example of context filtering the dCliente table

Figure 12: Filter applied by the active relationship

Going back to our case, it now becomes easier to understand that for each line of Country
we have a context. Think about what we've seen so far. How many lines of Country are being
evaluated in total? 360 lines which is the total number of clients (look again at figure 11 in
bottom left corner

Another example! Imagine that you want to know how many different customers made purchases.
from the company (client coverage/client positive engagement). This can be done by
a new measure with the count of distinct customer values
(DISTINCTCOUNT):

DAX Formula:
Customer Positivation =
DISTINCTCOUNT ( fVendas[Customer ID])
Figure 13: Supporting Table 3 (context of measures)

Now, I want you to stop and evaluate the total!

193 ≠ 192 + 90– ????

Remember that the total is not the sum of the other lines. It is the result of the measure (in this
case 'Positive Customers') without any filter applied. It is rational to think that it is
Possible 192 different customers have purchased headphones and 90 different customers.
They have bought MP4 & MP3. That is to say, there are customers who bought both.
Headphones for MP4 & MP3 and in total they should not be counted twice.

Calculate
This is our DAX queen!!! She is powerful because she can change contexts of
evaluation.

Imagine that in the billing visual by country (the one we just created),
we want to evaluate this revenue compared to the year 2018. Thus, we will have
two columns of revenue. So we can create a different context between the two
columns we need the assistance of CALCULATE. Let's create the new measure for the
context of 2018:

DAX Formula:
Faturamento 2018
CALCULATE ( [Revenue]; dCalendar[Year] = 2018 )
Figure 14: Support table 4 (context with CALCULATE)

Stages:
1. Create the Revenue measure for 2018
2. In 'Views' select 'Table' → Assign the fields
according to image

Here it is good to make it clear that we have visible contexts (the Country rows) and also the
additional context changed with the help of CALCULATE for the column of
Revenue 2018. The revenue amount for this column is naturally lower, because
the time interval is shorter.

Another way to use the CALCULATE function is to remove filters. This feature is
often used to compare values with their totals, for example. Imagine that
you want to present the percentage of a country's revenue against the total. This is a
a classic example to use our queen CALCULATE together with ALL is the
argument that allows one to escape contexts applied by the visual.

With the table we have, we can make a comparison between a measure with the ALL.
(which went out of the evaluation context) with another measure that respects the context. For this,
let's do it:

DAX Formula:
Fixed Billing ALL =
CALCULATE ( [Revenue]; ALL ( fSales) ) )
Figure 15: Support table 5 (context assessment with and without ALL)

Stages:
1. Create the Fixed Revenue ALL measure
2. In 'Views' select 'Table' → Assign the fields
according to the image

Well, with this fixed value we can create our percentage measure of the result by
total with another measure and use in the visual:

DAX Formula:
% Revenue =
DIVIDE ( [Revenue]; [Fixed Revenue ALL])

Figure 16: Support table 5 (context assessment with percentage comparison)

Tip:
Don't try to memorize all the formulas and mechanically copy what you've learned. Stop and
understand the logic, as there is a lot of support from 'help' within the software and the library
the formulas explained (many with examples) by Microsoft. Link:
[Link]

To test your knowledge, I want you to answer what happens to the values of the
revenue column if we use data segmentation on the page for the year of
2018

Figure 17: Interaction between data segmentation and visuals

Tell me, do you think the value changes or not!??

Figure 18: Result of applied filter

Note that the values in the Fixed Billing ALL column have not changed. As for the
Revenue respected the new context applied and the % Revenue also changed,
because it depends on the billing.

We can create a new measure using ALL, but now to ignore filters on
customer table:

DAX Formula:
Fixed Billing ALL =
CALCULATE ( [Revenue]; ALL ( dClient) ) )
Figure 19: Table with the new Fixed Billing ALL measure

I ask again! What happens if we filter the year 2018 in this case!???

Figure 20: Result of applied filter

Note that the values here have been filtered! Context filters were ignored here.
Clients (which do not contain dates).

Important:
Train a lot with ALL in your projects to understand and practice the concepts of this.
function that is essential in many cases.

In addition to all this, you can use more than one ALL in your formulas. Let's do it.
2 examples! In the first we will use ALL to ignore from specific columns of
same table and in the second to ignore two tables completely:

DAX Formula:
Fixed Billing ALL =
CALCULATE ( [Revenue]; ALL ( dClient[Country]; dClient[I
D Client] )

DAX Formula:
Fixed Revenue ALL =
CALCULATE ( [Revenue]; ALL ( dCustomer ); ALL ( dProduct )
))

Now imagine a scenario where you want to assess each of the countries with a country in
specific (in our case it will be Country = France). I believe it is a well assessment
common in your day to day to have to compare values between units, and this is quite
similar to what we are going to do.
We need the measure that ignores all filters and the country is France:

DAX Formula:
Revenue France Fixed =
CALCULATE ( [Revenue]; ALL ( dClient ); dClient[Country] )
France )

Figure 21: Table with the new Fixed Revenue Measure for France

Stages:
1. Create the Fixed Revenue measure for France
2. In "Views" select "Table" → Assign the fields
according to the image

There are several ways to write the same reasoning in DAX with ALL. One of the
the most commonly used way is with the FILTER function:

DAX Formula:
Fixed Billing France
CALCULATE (
Billing
FILTER ( ALL ( dCliente); dCliente[Country] = "France" )
)

The result of the two measures is the same!

In addition to ALL, we have another very important function for context evaluation.
ALLSELECTED. This function removes context filters from columns and rows in
current query, keeping all other external filters (data segmentation in
page, for example). Let's make a measure and use it in our table of
comparison:

DAX Formula:
Fixed Revenue ALLSELECTED =
CALCULATE ( [Revenue]; ALLSELECTED ( dClient ) )
Figure 22: Comparative table between ALL and ALLSELECTED

Stages:
Change France Fixed Revenue measure with FILTER
2. Create the Fixed Revenue measure ALLSELECTED
3. In 'Views' select 'Table' → Assign the fields
as per image

The third function we use in CALCULATE to change contexts is VALUES.

This function returns a single column table that contains the distinct values of
specified column and we use this column as filters. Shall we go to the example?

DAX Formula:
Faturamento VALUES
CALCULATE ( [Revenue]; VALUES ( dClient) ) )
Figure 23: Comparative table between Revenue and Revenue with VALUES

Stages:
1. Create the Revenue measure VALUES
2. In 'Views' select 'Table' → Assign the fields
as shown in the image

Note that in this case the VALUES respects the context filter.

Challenge: Average Churn Analysis

To practice everything that has been learned, we will create a report analyzing
of Average Churn of a fictional company. The Churn rate represents the amount of
clients who left the company during a certain period.

The total value of Churn is the sum of the Churn values:

DAX Formula:
=
SUM ( fChurn[Monthly Value])

To analyze this indicator, we will compare the actual value of the date with the average value.
of Churn. In the 'Analysis' of the graph visual, Power BI gives us the option to use a
average line as an indicator:

Figure 24: Creation of the Chart with the average Churn line

Stages:
1. Create the churn measure
2. In 'Views' select 'Area chart' → Assign the
fields as per image
3. In 'Analysis' click on 'Add' → Change color and transparency

The problem with using this type of analysis directly is that the comparison of what has been done is
made with the entire period. Furthermore, if we use a filter and want to display
only January 2017, for example, the reference line remains that of
whole period.

In DAX, we will create the Churn that respects the monthly limits in parts. First
we need the total Churn:

DAX Formula:
Average Churn =
CALCULATE ( [Churn]; ALL ( dCalendario) ) )

Now let's divide this total amount by the days that had churn (it may be different)
of the total days of the period, as some days may not have had Churn):

Average Churn =
CALCULATE (
[Churn] / DISTINCTCOUNT ( fChurn[Data Churn]);
ALL (dCalendar)
)

The result of our measurement in the visual will be:

Figure 25: Visualization of the graph with the analysis line and the measure

Stages:
1. Create the Average Churn metric
2. Assign the Average Churn Measure in Values

Imagine the scenario where you use date segmentation on your page, and that this
The graph respects the filters. What function can we use to make it work correctly?
this context? Remember ALLSLECTED?? It's her!

DAX Formula:
Average Churn
CALCULATE (
DISTINCTCOUNT ( fChurn[Data Churn])
ALLSELECTED ( dCalendario)
)

And the last question: How to evaluate in the context of the month separately? Remember of
VALUES?? If you don't remember, go back a little in the post and read it again, as it is she who
let's use:

DAX Formula:
Average Churn =
CALCULATE (
[Churn] / DISTINCTCOUNT ( fChurn[Data Churn]);
ALLSELECTED ( dCalendar);
VALUES ( dCalendar[Month/Year])
)

And the result of our chart with the final measurement looks like this:

Figure 26: Graph with the final measure

See the power we have with ALL/ALLSELECTED in conjunction with VALUES. If

some examples where you will probably have to use these functions:

Accumulated value by category

Contribution of a sales team per supervisor
Average combination assessed by month

Hey, can you imagine any of these challenges in your routine?

I hope you have learned and gained a good insight into DAX. Practice a lot to
fix the concepts and be able to use this knowledge in the challenges of your routine!! If
if you have any doubts or suggestions for themes for the Lives, leave them to us
comments.
Thanks guys! Hugs,

Leonardo

Share this post:

Share on Facebook
Share on LinkedIn
Share on Twitter
Share on Pinterest
Advanced DAX Profit and Loss Statement (DRE)
Next DAX Time Intelligence Functions (DATEADD, DATESYTD,
Previous Month…) Next

Attention: By using the site, you agree that we use cookies to provide you with a
better experience.

OK