@ashwani-kushwah
Intro to
DAX in
Power BI
Basics
Swipe Left
@ashwani-kushwah
DAX stands for Data Analysis
Expressions. It's the language
you use to create calculations
and formulas in Power BI.
In simpler terms:
DAX is the language you use to
tell Power BI what to calculate.
Swipe Left
@ashwani-kushwah
1. Create new column/table
Open any report or data set in which you
want to add column//table.
Then, go to Modellling tab and click on
Table.
Swipe Left
@ashwani-kushwah
Change table name, by clicking on the left
side of “=”.
In thr right side write formula : Distinct
orders = DISTINCT(Orders[Order ID])
Swipe Left
@ashwani-kushwah
After this new table will be created with all
the distinct Order ID.
DISTINCT function returns the distinct
values no matter how many times they
appear in data.
Swipe Left
@ashwani-kushwah
Now create new column
To create new column, open any table you
want.
Then under the Column tools, click on
New Column
Swipe Left
@ashwani-kushwah
In the formula bar, enter this formula :
Days to ship = DATEDIFF(Orders[Order
Date], Orders[Ship Date], DAY)
Swipe Left
@ashwani-kushwah
Using this formula, we are creating a new
column “Days to ship”.
DATEDIFF calculates the difference
between two dates.
Swipe Left
@ashwani-kushwah
Now add new column to calculate Rank by
Sales.
We are going to use RANKX functions.
We have 3 variations for this function.
Swipe Left
@ashwani-kushwah
Variation 1 - Rank highest
This formula will add new column with
sales rank.
In this formula Highest vales will have
Lowest rank.
Swipe Left
@ashwani-kushwah
Variation 2 - Rank lowest
We made minor change in above formula
by add one extra argument.
In this formula Lowest vales will have
Highest rank.
Swipe Left
@ashwani-kushwah
Variation 3 - Dense Rank
By default function will skip ranks.
Which means if in sales there are two
same values. It will assign same rank to
both the values. For exmaple rank 1 to
both value then it will skip rank 2 and next
value will have rank 3.
To change this we will add one extra
argument. i.e. Dense Swipe Left
@ashwani-kushwah
2. Calculated measures
In Power BI we can create calculated
measure by two methods.
we can add a quick measure
new measure using functions.
Swipe Left
@ashwani-kushwah
Quick measures
Go to the table, you want to add measure.
Then, under Table tools, click on Quick
measure
Swipe Left
@ashwani-kushwah
Under Select a calculation dropdown I am
selecting Total for category(filters not
applied)
Swipe Left
@ashwani-kushwah
Under Base value, select the column you
want to calculate measure on, numerical
value.
In the Category select the column by which
you calculate the measure.
Swipe Left
@ashwani-kushwah
Click on Add and new quick measure will
be added.
Remember since it is a calculation it will not
be visible in your data. but you can see it in
your data pane.
Swipe Left
@ashwani-kushwah
Calculated measure
To create a calculated measure.
Right click on the table.
Swipe Left
@ashwani-kushwah
In the formula bar, type this formula :
Avg Sales per Category =
CALCULATE(AVERAGE(Orders[Sales]),
ALLSELECTED(Orders[Product
Category]))
Swipe Left
Explanation of above function:
- AVERAGE(Orders[Sales]) : calculates the
average of all sales values
- ALLSELECTED(Orders[Product Category]) :
used to return all the values in a column,
ignoring any filters that might be applied to that
column
- CALCULATE() : It takes two arguments:
The first argument is the expression to be
calculated
The second argument is a filter modification
Together, they mean: Calculate the
average sales for all product categories,
regardless of any filters applied to the
product category.
@ashwani-kushwah
If you found this useful Follow me, on
Medium and LinkedIn.
Thank You
For Your
Attention
