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 Data Analysis Expressions (DAX). If you’re familiar with functions in MS Excel or SQL, many of the formulae in Power BI DAX will appear similar to you.

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?

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?

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, Context, and 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; 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: 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.

## 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]) MAXThis DAX function returns the maximum value in a column, including any logical values and numbers represented as text.Syntax MAX()Example=MAX([ResellerMargin])MAXAThis DAX function returns the maximum value in a column, including any logical values and numbers represented as text.Syntax MAXA()Example=MAXA(([PostalCode])MAXXThis 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]) SUMThis DAX function adds all the numbers in a column.Syntax SUM()Example=SUM(Sales[Amt]) AVERAGEThis DAX function returns the arithmetic mean of the values in a column.SyntaxAVERAGE()Example=AVERAGE(InternetSales[ExtendedSalesAmount]) SUMXThis DAX function returns the sum of an expression evaluated for each row in a table.SyntaxSUMX( , ) Example=SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight])AVERAGEXThis DAX function calculates the arithmetic mean of a set of expressions evaluated over a table.SyntaxAVERAGEX( , ) Example=AVERAGEX(InternetSales, InternetSales[Freight]+ InternetSales[TaxAmt])2. Count FunctionsDISTINCTCOUNTThis 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.SyntaxDISTINCTCOUNT()Example=DISTINCTCOUNT(ResellerSales_USD[SalesOrderNumber])COUNTThis 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.SyntaxCOUNT()Example=COUNT([ShipDate]) COUNTAThis is a DAX function used to return the count of items, in a column, that is not empty.SyntaxCOUNTA()Example=COUNTA('Reseller'[Phone]) COUNTROWSThis is a DAX function that counts the number of rows in the specified table, or in a table defined by an expression.SyntaxCOUNTROWS( )Example=COUNTROWS('Orders')COUNTBLANKThis is a DAX function that counts the number of blank cells in a column.SyntaxCOUNTBLANK()Example=COUNTBLANK(Reseller[BankName]) 3. Date-Time FunctionsDATEThis DAX function returns the specified date in Date-Time format.SyntaxDATE(<year>, <month>, <day>) Example=DATE(2019,12,17) HOURThis DAX function returns the specified hour as a number from 0 to 23 (12:00 A.M. to 11:00 P.M.).SyntaxHOUR() Example=HOUR('Orders'[TransactionTime]) TODAYThis DAX function returns the current date.SyntaxTODAY() NOWThis DAX function returns the current date and time in Date-Time format.SyntaxNOW()EOMONTHThis DAX function returns the date in Date-Time format of the last day of the month, before or after a specified number of months.SyntaxEOMONTH(, ) Example=EOMONTH("March 3, 2008",1.5)4. Mathematical FunctionsABSThis DAX function returns the absolute value of the number given.SyntaxABS() Example=ABS([DealerPrice]-[ListPrice])EXPThis DAX function returns the value of e raised to the power of the given number.SyntaxEXP() Example=EXP([Power])FACTThis DAX function returns the factorial of a number.SyntaxFACT() Example=FACT([Values])LNThis DAX function returns the natural log of the given number.SyntaxLN() Example=LN([Values])LOGThis DAX function returns the log with the base of the given number.SyntaxLOG(,)ExampleAll the following return the same result, 2.=LOG(100,10) =LOG(100) =LOG10(100) PIThis DAX function returns the value of Pi.SyntaxPI()POWERThis DAX function returns the value of the first argument raised to the power of the second argument.SyntaxPOWER(, <power>) Example=POWER(5,2)QUOTIENTThis DAX function performs division returns the integer part of the quotient. SyntaxQUOTIENT(, ) Example=QUOTIENT(5,2) SIGNThis DAX function returns the sign of a given number.SyntaxSIGN() Example=SIGN( ([Sale Price] - [Cost Price]) )SQRTThis DAX function returns the square root of the given number.SyntaxSQRT() Example=SQRT(25)5. Logical FunctionsANDThis DAX function performs logical AND(conjunction) on two expressions. For AND to return true, both conditions specified have to be fulfilled.SyntaxAND(,) 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". ORThis DAX function performs logical OR(disjunction) on two expressions. For OR to return true, either of the two conditions specified has to be fulfilled.SyntaxOR(,) 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".NOTThis DAX function performs logical NOT (negation) on given expression.SyntaxNOT() Example=NOT([CalculatedColumn1])For each row in Calculated Column1, the NOT function returns the logical opposite of the given value.IFThis DAX function tests a series of inputs for the one that fulfills the condition specified in the argument.SyntaxIF(logical_test>,, value_if_false) Example=IF([Calls]<200,"low",IF([Calls]<300,"medium","high"))IFERRORThis DAX function evaluates an expression and returns a specified value if the expression returns an error.SyntaxIFERROR(value, value_if_error) Example=IFERROR(25/0,9999) 6. Information FunctionsISBLANKThis DAX function returns TRUE or FALSE after checking whether a value is blank.SyntaxISBLANK(<value>) Example=IF( ISBLANK('CalculatedMeasures'[PreviousYearTotalSales]) , BLANK() , ( 'CalculatedMeasures'[Total Sales]-'CalculatedMeasures'[PreviousYearTotalSales] ) /'CalculatedMeasures'[PreviousYearTotalSales])ISNUMBERThis DAX function returns TRUE or FALSE after checking whether a value is numeric.SyntaxISNUMBER(<value>) Example=IF(ISNUMBER(0), "Is number", "Is Not number") ISTEXTThis DAX function returns TRUE or FALSE after checking whether a value is a text.SyntaxISTEXT(<value>) Example=IF(ISTEXT("text"), "Is Text", "Is Non-Text") ISNONTEXTThis DAX function returns TRUE or FALSE after checking whether a value is non-text.SyntaxISNONTEXT(<value>) Example=IF(ISNONTEXT("text"), "Is Non-Text", "Is Text")ISERRORThis DAX function returns TRUE or FALSE after checking whether a value is an error.SyntaxISERROE(<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 FunctionsCONCATENATEThis DAX function joins two text strings into one.SyntaxCONCATENATE(, ) Example=CONCATENATE("Hello ", "World")CONCATENATEXThis DAX function the result of an expression evaluated for each row in a table.SyntaxCONCATENATEX( , , [delimiter]) Example=CONCATENATEX(Employees, [FirstName] & “ “ & [LastName], “,”)FIXEDThis DAX function rounds a number to the specified number of decimals and returns the result as text.SyntaxFIXED(, , ) Example=FIXED([PctCost],3,1)REPLACEThis DAX function replaces part of a text string, based on the number of characters you specify, with a different text string.SyntaxREPLACE(, , , ) Example=REPLACE('New Products'[Product Code],1,2,"OB")SEARCHThis DAX function returns the number of the characters at which a specific text string is first found.SyntaxSEARCH(, [, [][, ]]) Example=SEARCH("n","printer")The formula returns 4 because "n" is the fourth character in the word "printer." UPPERThis DAX function returns a text string in all uppercase letters.SyntaxUPPER () Example=UPPER(['New Products'[Product Code])Creating your First Measure using Power BI DAXPrerequisite: 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. Posted in DevOpsTagged AWS, docker, jenkins, kubernetes, pipelines Post navigation ← How to Analyze Test Automation ResultsBuild an Android Mobile Application Using Azure DevOps → Recent Posts How to Compile and Run your first Java Program? Top Data Structures & Algorithms in Java That You Need to Know How to become a Certified Scrum Master Learn How To Use Map Function In Python With Examples How To Sort A Dictionary In Python : Sort By Keys , Sort By Values Cloud Devops (adsbygoogle = window.adsbygoogle || []).push({}); Tag Clouda2ensite active directory amazon aws Apache apt-get Auto scaling AWS aws cloud Azure centos cloud cloud devops cloud migration Cloudwatch debian Debian 7 ‘Wheezy’ devops docker EC2 ecs ELB Google cloud helm iSCSI jenkins k8s kubernetes LAMP linux linux for beginers MySQL NAS NAT NFS openfiler PHP stack pipelines redhat SAN virtual hosts vmware VPC VSAN yaml yum Search for: Categories Amazon AWS DevOps Linux Vmware Windows Search Search for: Theme: Sensible by modernthemes.net var _Hasync= _Hasync|| []; _Hasync.push(['Histats.start', '1,3646789,4,0,0,0,00010000']); _Hasync.push(['Histats.fasi', '1']); _Hasync.push(['Histats.track_hits', '']); (function() { var hs = document.createElement('script'); hs.type = 'text/javascript'; hs.async = true; hs.src = ('//s10.histats.com/js15_as.js'); (document.getElementsByTagName('head') || document.getElementsByTagName('body')).appendChild(hs); })(); /* <![CDATA[ */ var wpcf7 = {"apiSettings":{"root":"http:\/\/www.clouddevops.co.uk\/wp-json\/contact-form-7\/v1","namespace":"contact-form-7\/v1"},"recaptcha":{"messages":{"empty":"Please verify that you are not a robot."}},"cached":"1"}; /* ]]> */ ```