## 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:

**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;

*and*

**Row context***.*

**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:

**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.