DAX in Power BI

POWER-BI-DAX

DAX in Power BI

This blog is basically intended for users new to the Power BI Desktop and is intended to give you a speedy and simple stroll through on a formula-language called Data Analysis Expressions (DAX). In case you’re familiar with capacities in MS Excel or SQL, huge numbers of the formulae in this Power BI DAX Basics article will appear similar to you.

Having said that, here are the ideas, which form an integral part of all Power BI curricula, after learning which you ought to have a decent understanding of the most principal ideas in DAX.

  • Power BI DAX Basics: What is DAX?
  • Power BI DAX Basics:How does it work?
    • Syntax
    • Context
    • Functions
  • Power BI DAX Basics: Calculated Columns & Measures
    • Calculated Columns
    • Measures
    • Calculated Columns vs Measures
  • Power BI DAX Basics: Types of Functions in DAX
    • Aggregate Functions
    • Count Functions
    • Date-Time Functions
    • Mathematical Functions
    • Logical Functions
    • Information Functions
    • Text Functions
  • Power BI DAX Basics: Creating your First Measure

Power BI DAX Basics: What is DAX?

Things being what they are, we should start with the rudiments of Power BI DAX, alright?

It’s quite simple to create reports utilizing the Power BI Desktop, that will show significant bits of knowledge straight off the bat.

However, imagine a scenario where you have to break down growth percentage across all the product categories, for all the different date ranges. Or, you have to ascertain the yearly growth of your organization compared to market monsters?

Learning DAX will assist you with capitalizing on your charts and visualization and take care of real business problems.

DAX comprises of functions, operators, and constants that can be put into the form of formulae to calculate values with the help of data already present in your model.

The Power BI DAX includes a library of over 200 functions, operators, and constructs. Its library provides immense flexibility in creating measures to calculate results for just about any data analysis need.

Power BI DAX Basics: How does it work?

First of all, let me disclose to you how this works. We will, for the most part, be framing our understanding of Power BI DAX around three basic ideas: Syntax, Context, and Functions.

Of course, there are other important ideas in here, yet understanding these three will provide the best establishment on which you are going to construct your aptitudes.

Syntax

The Syntax comprises of various parts that make up a formula and how it’s written. Take a gander at this straightforward DAX formula.

When trying to understand a DAX formula, it is frequently useful to break down every one of the components into a language you think and talk every day. Thus, this formula incorporates the accompanying syntax components:

Syntax - Power BI DAX - Edureka

I. Total Sales is the measure name.

II. The equals sign operator (=) indicates the beginning of the formula.

III. SUM adds up all of the numbers in the column, Sales[SalesAmount].

IV. There are these parentheses () that surround an expression containing one or more arguments. All functions require at least one argument.

V. Sales is the table referenced.

VI. An argument passes a value to a function. The referenced column [SalesAmount] is an argument with which, the SUM function knows the column on which it has to aggregate a SUM.

Simply put, you can read it as, For the measure named Total Sales, calculate (=) the SUM of values in the [SalesAmount ] column in the Sales table.”

♠ The Power BI DAX editor includes a suggestions feature, which helps you create syntactically correct formulas by suggesting you the right elements.

Context

Context is one of the most important of the 3 DAX concepts. When one speaks of context, this may refer to one of the two types; Row context and Filter context.

Used predominantly whilst speaking of Measures, the Row-Context is most easily thought of as the current row. It applies whenever a formula has a function that applies filters to identify a single row in a table.

Filter-Context is a little more difficult to understand than the Row-Context. You can most easily think of the Filter-Context as one or more filters applied in a calculation. The Filter-Context doesn’t exist in the Row-context’s stead. Rather, it applies in addition to the former. Look at the following DAX formula.

This formula includes the following syntax elements:

Context - Power BI DAX - Edureka

I. The measure name Store Sales.

II. The equals sign operator (=) indicates the beginning of the formula.

III. The CALCULATE function evaluates an expression, as an argument.

IV. Parenthesis () surround an expression containing one or more arguments.

V. A measure [Total Sales] in the same table as an expression.

VI. A comma (,) separates the first expression argument from the filter argument.

VII. The fully qualified referenced column, Channel[ChannelName] is our Row-Context. Each row in this column specifies a channel, Store, Online, etc.

VIII. The particular value, Store is used as a filter. This is our Filter-Context.

This formula ensures that the Total Sales Measure are calculated only for rows in the Channel[ChannelName] Column with the value “Store”, as a filter.

Functions

Functions are predefined, structured and ordered formulae. They perform calculations using arguments passed on to them. These arguments can be numbers, text, logical values or other functions.

Power BI DAX Basics: Calculated Columns & Measures

In this blog, we’re going to be focusing on the Power BI DAX formulae used in calculations, in Measures and Calculated Columns.

Calculated Columns

At the point when you create a data model on the Power BI Desktop, you can broaden a table by creating new sections. The substance of the segments is characterized by a DAX expression, assessed row by row or with regards to the current row across that table.

In data models for DAX, however, all determined segments consume space in memory and are registered during table processing.

This behavior is useful in resulting in better user experience however it utilizes precious RAM and henceforth, is an unfortunate propensity in production on the grounds that each intermediate estimation is stored in RAM and squanders precious space.

Measures

There is another method of characterizing estimations in a DAX model, helpful on the off chance that you have to operate on aggregate qualities rather than on a row-by-row premise. These computations are measures. One of the requirements of DAX is that a measure should be characterized in a table. However, the measure doesn’t really have a place with the table. Along these lines, you can move a measure from one table to another one without losing its functionality.

Calculated Columns vs Measures

Measures and determined segments both use DAX expressions. The difference is the setting of assessment. A measure is assessed with regards to the cell assessed in a report or in a DAX query, whereas a determined section is figured at the row level within the table it has a place with.

Regardless of whether they seem to be similar, there is a big difference between determined sections and measures. The estimation of a determined segment is figured during a data refresh and utilizes the current row as a unique situation; it doesn’t rely upon user interaction in the report.

Hence, you have to define a calculated column whenever you want to do the following;

  • Place the calculated results in a slicer, or see results in rows or columns in a pivot table (as opposed to the values area), or in the axes of a chart, or use the result as a filter condition in a DAX query.
  • Define an expression that is strictly bound to the current row. For example, Price * Quantity cannot work on an average or on a sum of the two columns.
  • Categorize text or numbers. For example, a range of values for a measure.

A measure operates on aggregations of data characterized by the current setting, which relies upon the filter applied in the report –, for example, slicer, rows, and segments choice in a turn table, or tomahawks and filters applied to a chart.

So, you must define a measure whenever you want to display resulting calculation values that reflect user selections, such as;

  • When you calculate the profit percentage on a certain selection of data.
  • When you calculate ratios of a product compared to all products but keeping the filter both by year and region.

Power BI DAX Basics: Types of Functions in DAX

1. Aggregate Functions

MIN

This DAX function returns the minimum numeric value in a column, or between two scalar expressions.

Syntax

MIN(<column>)

Example

=MIN([ResellerMargin])

MINA

This DAX function returns the minimum value in a column, including any logical values and numbers represented as text.

Syntax

MINA(<column>)

Example

=MINA(([PostalCode])

MINX

This DAX function returns the minimum numeric value that results from evaluating an expression for each row of a table.

Syntax

MINX(<table>, < expression evaluated for each row>)

Example

=MINX( FILTER(InternetSales, InternetSales[SalesTerritoryKey] = 5), InternetSales[Freight] + InternetSales[TaxAmt])  

MAX

This DAX function returns the maximum value in a column, including any logical values and numbers represented as text.

Syntax

MAX(<column>)

Example

=MAX([ResellerMargin])

MAXA

This DAX function returns the maximum value in a column, including any logical values and numbers represented as text.

Syntax

MAXA(<column>)

Example

=MAXA(([PostalCode])

MAXX

This DAX function returns the maximum numeric value that results from evaluating an expression for each row of a table.

Syntax

MAXX(<table>, < expression evaluated for each row>)

Example

=MAXX( FILTER(InternetSales, InternetSales[SalesTerritoryKey] = 5), InternetSales[Freight] + InternetSales[TaxAmt])  

SUM

This DAX function adds all the numbers in a column.

Syntax

SUM(<column>)

Example

=SUM(Sales[Amt])

AVERAGE

This DAX function returns the arithmetic mean of the values in a column.

Syntax

AVERAGE(<column>)

Example

=AVERAGE(InternetSales[ExtendedSalesAmount])

SUMX

This DAX function returns the sum of an expression evaluated for each row in a table.

Syntax

SUMX(<table>, <expression evaluated for each row>)

Example

=SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight])

AVERAGEX

This DAX function calculates the arithmetic mean of a set of expressions evaluated over a table.

Syntax

AVERAGEX(<table>, <expression evaluated for each row>)

Example

=AVERAGEX(InternetSales, InternetSales[Freight]+ InternetSales[TaxAmt])

2. Count Functions

DISTINCTCOUNT

This is a DAX function used to return the distinct count of items in a column. So, if there are multiple numbers of the same item,  this function will count it as a single item.

Syntax

DISTINCTCOUNT(<column>)

Example

=DISTINCTCOUNT(ResellerSales_USD[SalesOrderNumber])

COUNT

This is a DAX function used to return the count of items in a column. So, if there are multiple numbers of the same item,  this function will count it as separate items and not a single item.

Syntax

COUNT(<column>)

Examples

=COUNT([ShipDate])

COUNTA

This is a DAX function used to return the count of items, in a column, that is not empty.

Syntax

COUNTA(<column>)

Example

=COUNTA('Reseller'[Phone])

COUNTROWS

This is a DAX function that counts the number of rows in the specified table, or in a table defined by an expression.

Syntax

COUNTROWS(<table>)

Example

=COUNTROWS('Orders')

COUNTBLANK

This is a DAX function that counts the number of blank cells in a column.

Syntax

COUNTBLANK(<column>)

Example

=COUNTBLANK(Reseller[BankName])

3. Date-Time Functions

DATE

This DAX function returns the specified date in Date-Time format.

Syntax

DATE(<year>, <month>, <day>)

Example

=DATE(2019,12,17)

HOUR

This DAX function returns the specified hour as a number from 0 to 23 (12:00 A.M. to 11:00 P.M.).

Syntax

HOUR(<datetime>)

Example

=HOUR('Orders'[TransactionTime])

TODAY

This DAX function returns the current date.

Syntax

TODAY()

NOW

This DAX function returns the current date and time in Date-Time format.

Syntax

NOW()

EOMONTH

This DAX function returns the date in Date-Time format of the last day of the month, before or after a specified number of months.

Syntax

EOMONTH(<start_date>, <months>)

Example

=EOMONTH("March 3, 2008",1.5)

4. Mathematical Functions

ABS

This DAX function returns the absolute value of the number given.

Syntax

ABS(<number>)

Example

=ABS([DealerPrice]-[ListPrice])

EXP

This DAX function returns the value of e raised to the power of the given number.

Syntax

EXP(<number>)

Example

=EXP([Power])

FACT

This DAX function returns the factorial of a number.

Syntax

FACT(<number>)

Example

=FACT([Values])

LN

This DAX function returns the natural log of the given number.

Syntax

LN(<number>)

Example

=LN([Values])

LOG

This DAX function returns the log with the base of the given number.

Syntax

LOG(<number>,<base>)

Example

All the following return the same result, 2.

=LOG(100,10)

=LOG(100)

=LOG10(100)

PI

This DAX function returns the value of Pi.