Data Analysis Expressions (DAX) is a formula-based language that is used throughout Microsoft Power BI for creating calculated measures, columns, and custom tables.
In this blog, we are going to look at how we can add new information from data that is already in your field in Microsoft Power BI using basic Data Analysis Expression (DAX) formulas.
The topics covered in this blog are:
- What Is Data Analysis Expression (DAX)?
- Data Analysis Expression (DAX) Formula Syntax
- Data Analysis Expression (DAX) function
- Data Analysis Expression (DAX) Statement
- Data Analysis Expression (DAX) Variables
- Why we need Data Analysis Expression?
What Is Data Analysis Expression (DAX)?
To do the basic calculation and data analysis on data in Power pivot, we use Data Analysis Expression (DAX). It is a formula-based language to compute calculated columns and calculated fields. Help to create new information in form of calculated columns and measures, table from data that is already existing in your table.
- DAX works on column values.
- It cannot modify or insert data.
- Helps to Create calculated columns and measures with DAX.
DAX mainly used to calculate on:
- Calculated Column
- Calculated Measures
- Calculated Quick New Measures
- Calculated Table
1) Calculated Columns
You can add a column in your data table in the field, the content of that column is defined by using DAX formulas and used for row-wise operation. Once applied DAX formula for rows in the table that same formula automatically applied to entire selected columns.
There are two ways to create a calculated column:
- Selecting New column from the ribbon.
- Right-click or select the (…) to any content in the Field and select New Column from the menu that displays
When you click the New column, the Formula bar at top of your report page, ready for creating a new column and enter a DAX formula.
2) Calculated Measures
you can add measures in your field in the table, the content of that measure is defined by DAX formulas. It is mainly used for Aggregate functions like Sum, Avg, Min, Max, etc. which is used for a calculated single value
There are two ways of calculating new measure:
- Selecting New measure from ribbon
- Right-click or select the (…) to any content in the Field and select New measure from the menu that display
When you click new measure, the Formula bar at top of your report page, ready for creating a new measure and enter a DAX formula.
Also Read: Our blog post on PL-300. Click here
3) Calculated Quick measure
you can also use Quick measures to speedily perform calculations. DAX common calculation is already provided, just one click and you can create a measure.
There are three ways of creating Quick measures are:
- Selecting New Quick measure from ribbon
- Right-click or select the (…) to any content in the Field and select New quick measure from the menu that display
- Right-click or select drop-down arrow to any value in the Values from that visual, and select New quick measure from the menu.
When you open New quick measure, the Quick measures window will display, select the calculation you have to do and the fields to do the calculation on.
Select a calculation field to see the calculation list of available Quick measures.
4) Calculated Table
you can add new tables on data that already present in the field. Instead of extracting values into your new table column from a data source, you create a (DAX) formula to define the table values.
Data Analysis Expression (DAX) Formula Syntax
DAX is a formula-based language.
- A = Name of measure or column
- B = Operator, beginning of DAX formula
- C = DAX Function
- D = Parenthesis for functions
- E = Table name
- F = Column name
Also Check: Our blog post on Microsoft Data Analyst Certification. Click here
Data Analysis Expression (DAX) function
DAX used functions, operators, and constant to build the formula for creating calculated columns and calculated measures. When you type function in the formula bar, then the list of all functions starting with that alphabet is shown
There are most commonly used types of DAX function
- Aggregate Functions: DAX has a number of aggregate functions like MIN, MAX, AVERAGE, SUM, SUMX
- Counting Functions: DAX has a counting function like DISTINCT COUNT, COUNT, COUNTA, COUNTROWS, COUNTBLANK
- Logical Functions: DAX has a logical function like AND, OR, NOT, IF, IFERROR
- TEXT Functions: REPLACE, SEARCH, UPPER, FIXED, CONCATENATE
- Date Functions: DAX has Date Functions like DATE, HOUR, WEEKDAY, NOW, EOMONTH
- Information Functions: DAX has an information function like ISBLANK, ISNUMBER, ISTEXT, ISNONTEXT, ISERROR
Data Analysis Expression (DAX) Statement
There are DAX statements used to form a Query that is:
- DEFINE: The DEFINE Statement is used when one or more evaluate statements and the DEFINE keyword include the definition of query variables. Syntax: DEFINE {<entity> [<name>] = <expression>}
- EVALUATE: The EVALUATE is used to execute the query. Syntax: EVALUATE <table>
- ORDER BY: The ORDER BY is used in EVALUATE statement to defines sort the order in ascending and descending way. Syntax: ORDER BY {<expression> [{ASC | DESC}]}
- VAR: The VAR is used to define the variables in an expression. Syntax: VAR <name> = <expression>
- RETURN: The RETURN is used to return the variable used in the expression. Syntax: VAR <name> = <expression> RETURN <result expression>
Also Check: What is Power bi? Click here
Data Analysis Expression (DAX) Variables
In DAX calculation, we used variables to make the calculations easy and readable to understand. When we write any complex query using DAX Function the variable helps to break the complex calculation into smaller.
Why we need Data Analysis Expression?
- Using DAX, you can make your besuited dashboard.
- Use DAX calculated columns whenever you will not able to implement the calculation in a Power Query
- Improve the way of thinking on how to manipulate data efficiently.
- It is very simple to use
- It solves business problems.
References
- Introduction to Microsoft Power BI Platform | Everything You Must Know
- Exam PL-300: Microsoft Power BI Data Analyst | A Replacement Of DA-100
- Microsoft Certified Data Analyst Associate [PL-300] Step By Step Activity Guides (Hands-On Labs)
- Getting Started with Microsoft Azure Power BI: Case Study
- Microsoft Power BI VS Tableau | Which one is Better?
Next Steps to begin with PL-300 Certification:
In our PL-300 Certification Training Program, we’ll cover 10+ Hands-On Labs. If you wish to start your journey towards becoming a Microsoft Certified: Power BI Data Analyst Associate, try our FREE CLASS.
Ben says
Great .Kindly share pdf kit of the same
Rahul Dangayach says
Hi Ben,
We are glad that you liked our blog!
Please stay tuned for more informative blogs.
Regarding the Kit, there is no kit available, but we do have the course for DA100, If you would like to enroll please click on the link below:
http://k21academy.com/da100
Hope this helps.
Thanks & Regards
Rahul Dangayach
Team K21 Academy