Power BI is all about Data Analytics, Data Visualization, and Business Intelligence. Power BI is a Business Intelligence (BI) tool and a Data Visualization software developed by Microsoft to help you with real-time high-level analytics, extensive modeling, and custom development. Simply put, Power BI makes working with data really easy. However, when using Microsoft Power BI, you’ll frequently discover that you need to create formulae and expressions to analyze data and calculate values to solve real business problems. This is where DAX Power BI comes in.
Data Analysis Expressions (DAX) is a collection of functions, operators, and constants used to create formulae and expressions in Power BI and various other Data Analysis services such as Microsoft SQL Server, Excel Power Pivot, etc. The Power BI DAX comprises over 200 functions, operators, and constants that provide immense flexibility in Data Analysis tasks. This blog will give you a quick and easy walk-through of Power BI DAX. But before getting started with Power BI DAX, let’s discuss this robust Business Intelligence (BI) tool in brief.
What is Power BI?
Power BI is a Business Intelligence (BI) tool and a Data Visualization platform offered by Microsoft that allows organizations to analyze business data and generate reports. Power BI comes with a set of built-in tools, apps, and connectors that can deeply delve and work with data to provide actionable insights, immersive visuals, and interactive reports.
Power BI is actually self-service Business Intelligence which means that you can easily aggregate data, analyze data, visualize data, and produce some fantastic-looking visual reports. Power BI lets you pull data in from multiple sources such as Oracle, SAP, or a Data Warehouse of your choice. It can handle everything from your simple Excel file all the way to massive amounts of data. You can leverage the Power BI Chart, Graphs, KPIs, Reports, and Dashboards to analyze the data and get interactive insights.
Power BI can be used as both, a standalone Desktop Software and as a fully-managed Cloud-based Web Service. Power BI Desktop can be downloaded for free from its official website, but the Power BI Service is based on a subscription model that charges customers based on their usage. Power BI is also available on mobile for those who want to keep an eye on their data while on the go.
What is DAX?
Data Analysis Expression Language (DAX) is an acronym for Data Analysis Expression Language. Microsoft’s data analysis products, including Power BI, Excel Power Pivot, SQL Server Analysis Services Tabular Edition, and Azure Analysis Services, all employ this language. The programming language is a hybrid of T-SQL, Excel formulas, and C#.
Because DAX is an expression language, the majority of it is written as a formula after an equal sign (=). There are hundreds of functions that can be utilized to accomplish a variety of tasks. The expression language, on the other hand, has some essentials and basics.
What is Power BI DAX?
Data Analysis Expressions (DAX) is a collection of functions, operators, and constants used to create formulae and expressions in Power BI, Microsoft SQL Server, and Excel Power Pivot. You can specify several arguments in a DAX formula and you can further execute DAX functions to perform a specific operation on the values in an argument. In simple words, the Power BI DAX are predefined codes for performing analytics-specific data tasks.
The Power BI DAX Library houses over 200 functions, operators, and constants that provide immense flexibility in Data Analysis tasks. On top of that, new functions and functionalities are regularly added to DAX in order to accommodate new features. Power BI DAX programming formulae have two data types, Numeric and Non-numeric. The numeric data type includes integers, decimals, currency, etc. Whereas, the non-numeric data type consists of strings and binary objects. Power BI DAX expressions allow Data Analysts to utilize the data sets to their fullest potential.
Why is DAX Power BI Important?
In Power BI, it’s quite easy to create reports that’ll provide actionable insights from all the available data. But, what if you need to level up and analyze the growth percentage across different age groups, for different date ranges? Or, what if you need to calculate the annual growth of your company compared to market giants? To use advanced calculations in your reports, you would need DAX Power BI.
You can use DAX Power BI formulas to create new metrics and exclusive visualizations, and use them for answering custom and specific queries. With such unique insights into data, you begin to solve real business problems that affect your bottom line. Thus, DAX Power BI paves the way for a smarter and more intelligent approach to Data Analysis. Let’s take a look at some of the key features of DAX Power BI.
Prerequisite for Power BI DAX
The implementation of DAX expressions is similar to creating formulas in Microsoft Excel. Knowing how to use an Excel spreadsheet is advantageous, but you can use DAX without knowing how to use an Excel spreadsheet. You should be familiar with the Power BI Desktop as well as the fundamentals of measurements, statistics, and probability in order to grasp the formulae and calculations.
DAX Power BI Syntax
Before you start creating your own Power BI DAX formula, it is important to understand the DAX formula syntax. Let’s take a look at the various elements that make up a DAX formula. For example, here’s a simple DAX Power BI formula.
Here’s what each syntax element labeled in the image represents:
- A: It represents the name of the measure or metric or calculation (Total Sales).
- B: It is the equals sign (=) operator/function which indicates the start of the DAX formula.
- C: The DAX function SUM is used to add up all the values in the given field ([SalesAmount]) of a table (Sales).
- D: Parenthesis () is used to enclose an expression that contains one or more arguments.
- E: It represents the referenced table (Sales).
- F: It represents the referenced column ([SalesAmount]).
DAX Power BI Functions Categories
DAX Power BI functions are predefined formulas used to perform calculations with arguments. An argument simply passes a value to a function. Arguments can either be other functions, expressions, another formula, text, numbers, logical values such as TRUE or FALSE, or constants.
In Power BI, you can use the following categories of functions to analyze data and create new columns and measures.
DAX Power BI Functions: Date and Time Functions
These functions are used to carry out calculations on the date and time values. Below are some of the functions of this category.
- CALENDAR: Returns a table containing a contiguous set of dates with a single column named “Date.” The dates in the range are from the start date to the finish date, inclusive of those two dates.
Syntax:
CALENDAR(<start_date>, <end_date>)
Example: = CALENDAR (DATE (2015, 1, 1), DATE (2021, 12, 31))
- CALENDARAUTO: Returns a table containing a contiguous set of dates with a single column named “Date.” The date range is calculated automatically based on the model’s data.
Syntax:
CALENDARAUTO([fiscal_year_end_month])
Here the term [fiscal_year_end_month] represents an integer between 1 and 12 as a result. If not given, defaults to the value set in the current user’s calendar table template, if one exists; otherwise, it defaults to 12.
Example: CALENDARAUTO(3)
- DATE: The supplied date is returned in datetime format.
Syntax:
DATE(<year>, <month>, <day>)
Example: = DATE(2009,7,8)
The above formula returns the date July 8, 2009.
- DATEVALUE: Converts a text-based date to a datetime-based date.
Syntax:
DATEVALUE(date_text)
Example: = DATEVALUE(“8/1/2009”)
Depending on the model locale and how dates and times are presented, the above example returns a varied datetime value.
a) The example returns a datetime value equivalent to January 8th, 2009, in date/time settings where the day precedes the month.
b) The example returns a datetime value equivalent to August 1st, 2009, in date/time settings where the month precedes the day.
- DAY: Returns the day of the month, a number from 1 to 31.
Syntax:
DAY(<date>)
Example: = IF( DAY([SalesDate])=10,”promotion”,””)
The above expression returns the day that each sales order was placed, and flags the row as a promotional sale item if the order was placed on the 10th of the month.
Below are some other important Date and time Power BI DAX Functions:
- HOUR
- MINUTE
- MONTH
- SECOND
- TIME
- TIMEVALUE
- TODAY
- WEEKDAY
- YEAR
DAX Power BI Functions: Information Functions
These functions provide certain information with respect to the data values stored in the rows and columns. It evaluates the condition given in the function for the arguments passed and returns either TRUE or FALSE depending on the evaluation. Below are some of the functions of this category.
- CONTAINS: If values for all referred columns exist or are contained in those columns, the method returns true; otherwise, it returns false.
Syntax:
CONTAINS(<table>, <columnName>, <value>[, <columnName>, <value>]…)
Example: = CONTAINS(InternetSales, [ProductKey], 214, [CustomerKey], 11185)
The above example sets a metric that tells you if there were any concurrent Internet sales of product 214 and customer 11185.
- CUSTOMDATA: The value of the CustomData property in the connection string is returned.
Syntax:
CUSTOMDATA()
Example: = IF(CUSTOMDATA()=”OK”, “Correct Custom data in connection string”, “No custom data in connection string property or unexpected value”)
The above DAX formula verifies if the CustomData property was set to “OK”.
- ISBLANK: Returns TRUE or FALSE depending on whether a value is blank.
Syntax:
ISBLANK(<value>)
Example: = ISBLANK(StudName[StudName])
ISBLANK function to check whether the value in the StudName column is blank or not and returns the TRUE or FALSE.
- ISERROR: Returns TRUE or FALSE depending on whether a value is an error.
Syntax:
ISERROR(<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])
)
The ratio of total Internet sales to total reseller sales is calculated in the above example. To check for problems like division by zero, the ISERROR function is utilised. A blank is returned if there is an error; otherwise, the ratio is returned.
Below are some other important Information DAX Power BI Functions:
- ISEVEN
- ISLOGICAL
- ISNUMBER
- ISODD
- ISTEXT
- LOOKUPVALUE
- USERNAME
DAX Power BI Functions: Time Intelligence Functions
These functions assist you in creating computations that make use of the built-in calendar and date knowledge. You can make meaningful sales, inventory, and other calculations over a fixed period of time. Below are some of the functions of this category.
- CLOSINGBALANCEMONTH: In the current context, evaluates the expression on the last day of the month.
Syntax:
CLOSINGBALANCEMONTH(<expression>,<dates>[,<filter>])
Example: = The following sample formula below provides a measure that estimates the product inventory’s ‘Month End Inventory Value.’
CLOSINGBALANCEMONTH(SUMX(ProductInventory,ProductInventory[UnitCost]*ProductInventory[UnitsBalance]),DateTime[DateKey])
- CLOSINGBALANCEYEAR: Evaluates the expression at the last date of the year in the current context.
Syntax:
CLOSINGBALANCEYEAR(<expression>,<dates>[,<filter>][,<year_end_date>])
Example: The sample formula below provides a measure that estimates the product inventory’s ‘Year End Inventory Value.’
=CLOSINGBALANCEYEAR(SUMX(ProductInventory,ProductInventory[UnitCost]*ProductInventory[UnitsBalance]),DateTime[DateKey])
- DATEADD: Returns a table with a column of dates that have been pushed forward or backward in time by the provided number of intervals from the current context’s dates.
Syntax:
DATEADD(<dates>,<number_of_intervals>,<interval>)
Example: = DATEADD(DateTime[DateKey],-1,year)
The above formula calculates dates that are one year before the dates in the current context.
Returns a table with a column of dates that starts on the supplied start date and ends on the specified end date. This function can be used to filter the output of the CALCULATE function. It can be used to filter an expression by a specific date period.
Syntax:
DATESBETWEEN(<Dates>, <StartDate>, <EndDate>)
Example:
Customers LTD =
CALCULATE(
DISTINCTCOUNT(Sales[CustomerKey]),
DATESBETWEEN(
'Date'[Date],
BLANK(),
MAX('Date'[Date])
)
)
Below are some other important Time Intelligence Functions DAX Power BI functions:
- DATESINPERIOD
- DATESQTD
- DATESYTD
- ENDOFMONTH
- ENDOFYEAR
- FIRSTDATE
- FIRSTNONBLANK
- LASTDATE
- NEXTQUARTER
- LASTNONBLANK
DAX Power BI Functions: Logical Functions
These functions are used to logically evaluate an expression or argument and return TRUE or FALSE depending on whether or not the condition is satisfied. Below are some of the functions of this category.
- AND
- FALSE
- IF
- IFERROR
- IN
- NOT
- OR
- SWITCH
- TRUE
DAX Power BI Functions: Mathematical Functions
These functions are used to perform all sorts of mathematical operations on the given values. Below are some of the functions of this category.
- ABS
- ACOS
- ASIN
- ATAN
- CEILING
- COMBIN
- COS
- CURRENCY
- DEGREES
- DIVIDE
- EVEN
- EXP
- FACT
DAX Power BI Functions: Statistical Functions
These functions are used to carry out statistical and aggregation operations on data values. Below are some of the functions of this category.
- ADDCOLUMNS
- APPROXIMATEDISTINCTCOUNT
- AVERAGE
- AVERAGEA
- BETA.INV
- CHISQ.INV
- CONFIDENCE.NORM
- COUNT
- COUNTBLANK
- COUNTROWS
- CROSSJOIN
- DISTINCTCOUNT
- EXPON.DIST
- GENERATE
- GENERATEALL
- GEOMEAN
- GEOMEANX
- MAX
- MEDIAN
- MEDIANX
- MIN
- MINX
DAX Power BI Functions: Text Functions
These functions evaluate string values. Below are some of the functions of this category.
- BLANK
- CODE
- COMBINEVALUES
- CONCATENATE
- EXACT
- FIND
- FORMAT
- LEFT
- MID
- REPLACE
- RIGHT
- SEARCH
- SUBSTITUTE
- TRIM
- UNICHAR
DAX Power BI Functions: Parent/Child Functions
These functions are used for data values that are a part of a parent/child hierarchy.
- PATH
- PATHCONTAINS
- PATHITEM
- PATHITEMREVERSE
- PATHLENGTH
DAX Power BI Functions: Other Functions
In addition to the above-mentioned functions, there are a lot of functions that do not fit in a particular category.
- DATATABLE
- ERROR
- EXCEPT
- GENERATESERIES
- GROUPBY
- INTERSECT
- ISEMPTY
- ISSELECTEDMEASURE
- NATURALINNERJOIN
- NATURALLEFTOUTERJOIN
- SELECTEDMEASUREFORMATSTRING
- SUMMARIZECOLUMNS
- UNION
- VAR
DAX Power BI Context
There are 2 types of DAX contexts.
- Row Context: As the name suggests, row context takes into account a specific row that has been filtered in a DAX expression. This context focuses on performing the operation on the current row. This type of context is usually applied to the measure rows.
- Filter Context: The filter context takes focusing on values one step ahead. In the filter context, a filter is applied by the DAX expression to focus on specific values within a row. The filter context is applied along with the row context to further constrict the scope of calculations to specific values.
Filter context is applied with functions like CALCULATE, FILTER, RELATED, ALL, etc.
Power BI DAX Basics: Calculated Columns & Measures
By now, you must have understood the importance and various aspects of DAX Power BI. Before concluding, let’s delve a little into the implementation of DAX and see how Power BI DAX formulae are used in Measures and Calculated Columns calculations.
Calculated Columns
A Power BI data model has data stored in rows and columns. The DAX expressions evaluated in the context of rows are used to define the content of the columns. In simple words, a Calculated Column is evaluated at the row level within the table. All these Calculated Columns occupy some space in memory and are computed during table processing.
This practice results in a better user experience, but at the same time, each calculation uses your RAM and wastes precious space in the memory. You need to define a Calculated Column whenever you want to do the following:
- Place the calculated results in a slicer, see results in a pivot table, or in the axes of a chart, or use the result as a filter condition in a Power BI DAX query.
- Define an expression that is strictly bound to the current row. For example,
Price * Quantity will not work on an average or on a sum of the 2 columns.
- Categorize text or numbers.
Measures
Measures are another method of defining calculations in a DAX data model. They operate on aggregate values of data defined by the current context instead of on a row-by-row basis. A Measure is computed in the context of the cell evaluated in a report or in a DAX query. A Measure needs to be defined in a table, however, the measure does not really belong to the table. This means a measure can be moved from one table to another without losing its functionality.
You need to define a Measure whenever you want to display results that reflect user selections, such as:
- Calculate the profit percentage on a certain selection of data.
- Calculate ratios of a product compared to other products and keep the filter both by year and by region.
Conclusion
Power BI is Microsoft’s Business Intelligence tool used by Data Professionals all over the world to examine data from multiple sources and create attractive reports and dashboards. Making reports using the Power BI functionalities of data importing, transforming, and visualizing is a smooth experience. However, some effective DAX Power BI formulas will help you get the most out of your data.
This blog introduced you to Power BI and took you through various aspects of DAX Power BI. Power BI DAX comprises functions, operators, and constants that help you calculate results for just about any Data Analysis need. Power BI makes Business Analysis more efficient through intuitive, interactive, and easy-to-use services. Moreover, analyzing and visualizing your data by loading it from a Data Warehouse to Power BI can be cumbersome. This is where Hevo comes in.
visit our website to explore hevo
Hevo Data with its strong integration with 100+ Sources & BI tools allows you to not only export data from sources & load data to the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools such as Power BI.
Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!
Share your experience of understanding DAX Power BI in the comments section below.
Raj, a data analyst with a knack for storytelling, empowers businesses with actionable insights. His experience, from Research Analyst at Hevo to Senior Executive at Disney+ Hotstar, translates complex marketing data into strategies that drive growth. Raj's Master's degree in Design Engineering fuels his problem-solving approach to data analysis.
