This blog is essentially designed for users new to the * Power BI Desktop* and is meant to give you a quick and easy walkthrough on a formula-language called

**. If you’re familiar with functions in**

*Data Analysis Expressions (DAX)**or*

**MS Excel***, many of the formulae in*

**SQL***will appear similar to you.*

**Power BI DAX**Having said that, here are the concepts, which form an integral part of all * Power BI curricula*, after learning which you should have a good understanding of the most fundamental concepts in DAX.

**What is DAX?****How does it work?****Calculated Columns & Measures****Types of Functions in Power BI DAX****Creating your First Measure using Power BI DAX**

**What is DAX?**

So, it’s quite easy to create reports using the Power BI Desktop, that’ll show valuable insights straight off the bat.

But, what if you need to analyze growth percentage across all the product categories, for all the different date ranges? Or, you need to calculate the annual growth of your company compared to market giants?

Learning DAX will help you get the most out of your * charts and visualisation* and solve 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.

**How does it work?**

First of all, let me explain to you how this works. We shall, for the most part, be framing our understanding of Power BI DAX around three fundamental concepts: ** Syntax**,

**, and**

*Context***.**

*Functions*Of course, there are other important concepts in here, but understanding these three will provide the best foundation on which you are going to build your skills.

**Syntax**

The * Syntax* comprises of various components that make up a formula and how it’s written. Look at this simple DAX formula.

When trying to understand a DAX formula, it is often helpful to break down each of the elements into a language you think and speak every day. So, this formula includes the following syntax elements:

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

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

When you create a data model on the Power BI Desktop, you can extend a table by creating new columns. The content of the columns is defined by a DAX expression, evaluated row by row or in the context of the current row across that table.

In data models for DAX, however, all calculated columns occupy space in memory and are computed during table processing.

This behavior is helpful in resulting in better user experience but it uses precious RAM and hence, is a bad habit in production because each intermediate calculation is stored in RAM and wastes precious space.

**Measures**

There is another way of defining calculations in a DAX model, useful if you need to operate on aggregate values instead of on a row-by-row basis. These calculations are measures. One of the requirements of DAX is that a measure needs to be defined in a table. However, the measure does not really belong to the table. So, you can move a measure from one table to another one without losing its functionality.

**Calculated Columns vs Measures**

Measures and calculated columns both use DAX expressions. The difference is the context of evaluation. A measure is evaluated in the context of the cell evaluated in a report or in a DAX query, whereas a calculated column is computed at the row level within the table it belongs to.

Even if they look similar, there is a big difference between calculated columns and measures. The value of a calculated column is computed during a data refresh and uses the current row as a context; it does not depend on 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 defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a pivot table, or axes 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.

**Types of Functions in Power BI DAX**

**1. Aggregate Functions**

**MIN**

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

**Syntax**

` MIN(`

**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(`

**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(`

```
, < 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(`)

**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(`)

**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(`

`, < 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(`)

**Example**

`=SUM(Sales[Amt]) `

**AVERAGE**

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

**Syntax**

`AVERAGE(`)

**Example**

`=AVERAGE(InternetSales[ExtendedSalesAmount]) `

**SUMX**

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

**Syntax**

`SUMX(`

`, `)

**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(`

`, `)

**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(`)

**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(`)

**Example**

`=COUNT([ShipDate]) `

**COUNTA**

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

**Syntax**

`COUNTA(`)

**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(`

`)`

**Example**

`=COUNTROWS('Orders')`

**COUNTBLANK**

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

**Syntax**

`COUNTBLANK(`)

**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(`)

**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(`, )

**Example**

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

**4. Mathematical Functions**

**ABS**

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

**Syntax**

`ABS(`)

**Example**

`=ABS([DealerPrice]-[ListPrice])`

**EXP**

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

**Syntax**

`EXP(`)

**Example**

`=EXP([Power])`

**FACT**

This DAX function returns the factorial of a number.

**Syntax**

`FACT(`)

**Example**

`=FACT([Values])`

**LN**

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

**Syntax**

`LN(`)

**Example**

`=LN([Values])`

**LOG**

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

**Syntax**

`LOG(`, )

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

**Syntax**

`PI()`

**POWER**

This DAX function returns the value of the first argument raised to the power of the second argument.

**Syntax**

`POWER(`, <power>)

**Example**

`=POWER(5,2)`

**QUOTIENT**

This DAX function performs division returns the integer part of the quotient.

**Syntax**

`QUOTIENT(`, )

**Example**

`=QUOTIENT(5,2) `

**SIGN**

This DAX function returns the sign of a given number.

**Syntax**

`SIGN(`)

**Example**

`=SIGN( ([Sale Price] - [Cost Price]) )`

**SQRT**

This DAX function returns the square root of the given number.

**Syntax**

`SQRT(`)

**Example**

`=SQRT(25)`

**5. Logical Functions**

**AND**

This DAX function performs logical AND(conjunction) on two expressions. For AND to return true, both conditions specified have to be fulfilled.

**Syntax**

`AND(`,)

**Example**

`=IF(AND(10 > 9, -10 < -1), "All true", "One or more false"`

`Because both conditions, passed as arguments, to the AND function are true, the formula returns "All True". `

**OR**

This DAX function performs logical OR(disjunction) on two expressions. For OR to return true, either of the two conditions specified has to be fulfilled.

**Syntax**

`OR(`,)

**Example**

`=IF(OR(10 > 9, -10 >-1), "True", "False"`

`Because one of the conditions, passed as arguments, to the OR function is true, the formula returns "True".`

**NOT**

This DAX function performs logical NOT (negation) on given expression.

**Syntax**

`NOT(`)

**Example**

`=NOT([CalculatedColumn1])`

`For each row in Calculated Column1, the NOT function returns the logical opposite of the given value.`

**IF**

This DAX function tests a series of inputs for the one that fulfills the condition specified in the argument.

**Syntax**

`IF(logical_test>,`, value_if_false)

**Example**

`=IF([Calls]<200,"low",IF([Calls]<300,"medium","high"))`

**IFERROR**

This DAX function evaluates an expression and returns a specified value if the expression returns an error.

**Syntax**

`IFERROR(value, value_if_error) `

**Example**

`=IFERROR(25/0,9999) `

**6. Information Functions**

**ISBLANK**

This DAX function returns TRUE or FALSE after checking whether a value is blank.

**Syntax**

`ISBLANK(<value>) `

**Example**

`=IF( ISBLANK('CalculatedMeasures'[PreviousYearTotalSales]) , BLANK() , ( 'CalculatedMeasures'[Total Sales]-'CalculatedMeasures'[PreviousYearTotalSales] ) /'CalculatedMeasures'[PreviousYearTotalSales])`

**ISNUMBER**

This DAX function returns TRUE or FALSE after checking whether a value is numeric.

**Syntax**

`ISNUMBER(<value>) `

**Example**

`=IF(ISNUMBER(0), "Is number", "Is Not number") `

**ISTEXT**

This DAX function returns TRUE or FALSE after checking whether a value is a text.

**Syntax**

`ISTEXT(<value>) `

**Example**

`=IF(ISTEXT("text"), "Is Text", "Is Non-Text") `

**ISNONTEXT**

This DAX function returns TRUE or FALSE after checking whether a value is non-text.

**Syntax**

`ISNONTEXT(<value>) `

**Example**

`=IF(ISNONTEXT("text"), "Is Non-Text", "Is Text")`

**ISERROR**

This DAX function returns TRUE or FALSE after checking whether a value is an error.

**Syntax**

`ISERROE(<value>) `

**Example**

`=IF( ISERROR( SUM('ResellerSales_USD'[SalesAmount_USD]) /SUM('InternetSales_USD'[SalesAmount_USD]) ) , BLANK() , SUM('ResellerSales_USD'[SalesAmount_USD]) /SUM('InternetSales_USD'[SalesAmount_USD]) ) `

**7. Text Functions**

**CONCATENATE**

This DAX function joins two text strings into one.

**Syntax**

`CONCATENATE(`, )

**Example**

`=CONCATENATE("Hello ", "World")`

**CONCATENATEX**

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

**Syntax**

`CONCATENATEX(`

`, `, [delimiter])

**Example**

`=CONCATENATEX(Employees, [FirstName] & “ “ & [LastName], “,”)`

**FIXED**

This DAX function rounds a number to the specified number of decimals and returns the result as text.

**Syntax**

`FIXED(`, , )

**Example**

`=FIXED([PctCost],3,1)`

**REPLACE**

This DAX function replaces part of a text string, based on the number of characters you specify, with a different text string.

**Syntax**

`REPLACE(`, , , )

**Example**

`=REPLACE('New Products'[Product Code],1,2,"OB")`

**SEARCH**

This DAX function returns the number of the characters at which a specific text string is first found.

**Syntax**

`SEARCH(`, [, [][, ]])

**Example**

`=SEARCH("n","printer")`

`The formula returns 4 because "n" is the fourth character in the word "printer." `

**UPPER**

This DAX function returns a text string in all uppercase letters.

**Syntax**

`UPPER (`)

**Example**

`=UPPER(['New Products'[Product Code])`

**Creating your First Measure using Power BI DAX**

**Prerequisite:** You’ll need to open **this given Power BI Desktop file**.

Since I’m assuming this is going to be your first, I’ll be writing this bit in great detail for you to follow along.

- In the field list of the
**Report View**, right-click on the **Sales** table, followed by the **New Measure**.
- Replace
**Measure** by typing a new measure name **Previous Quarter Sales, **in the *Formula Bar*.
- In this formula, you want to use the
**CALCULATE** function. So, after the equals sign, type the first few letters **CAL**, and then double-click the function you want to use.
**The CALCULATE function has at least two arguments.** The first is the expression to be evaluated, and the second is a *Filter*.
- After the opening
**parenthesis** **(** for the **CALCULATE** function, type **SUM** followed by another opening parenthesis **( **to pass an argument to the **SUM** function.
- Begin typing
**Sal**, and then select **Sales[SalesAmount]**, followed by a closing parenthesis **)**. This is the first expression argument for our **CALCULATE** function.
- Type a
**comma (,)** followed by a space to specify the first filter, and then type **PREVIOUSQUARTER**. This will be our filter.
- You’ll use the
**PREVIOUSQUARTER** time intelligence function to filter **SUM** results by the previous quarter.
- After the opening parenthesis
**(** for the PREVIOUSQUARTER function, type **Calendar[DateKey]**.
- The
**PREVIOUSQUARTER** function has one argument, a column containing a contiguous range of dates. In our case, that’s the **DateKey** column in the Calendar table.
- Make sure both the arguments being passed to the PREVIOUSQUARTER and the CALCULATE function are closed by typing two closed parentheses
**))**.
- Your formula should now look something like the following;

**Previous Quarter Sales = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSQUARTER(Calendar[DateKey]))**
- Click the checkmark in the formula bar or press Enter to validate the formula.

Once you’ve added that to your model, voila! You just created a measure using DAX, and not an easy one at that.

What this formula does is **calculate the total sales for the previous quarter, depending on the filters applied in a report.**

So, let’s if we had to put **SalesAmount** and our new **Previous Quarter Sales** measure in a chart, and then added **Year** and **QuarterOfYear** as *Slicers,* we’d get something in the likes of the following;

*Now that you have a basic understanding of the concepts in Power BI DAX, you can begin creating DAX formulas for measures on your own. Indeed, it can be a little tricky to learn, but DAX has been around for several years and there are many resources available on the web. After reading through this blog and a little experimentation, you can learn to find business solutions through Power BI DAX.*

The post Starting out with DAX in Power BI appeared first on Edureka.