Over the last five years, Power BI has gone through a lot of changes. Ranging from DAX and Power Query to the latest version, M Query. VBA in Excel is similar to Power BI. Data transformations, AI, Big Data, and Blockchain, are buzzwords in every industry, including operational staff and C-suite executives. Large data sets must be efficiently visualized to provide managers with better insights and remain competitive. In this case, data visualization tools like Power BI are critical for organizing and presenting large datasets effectively and summarizing. VBA in Excel is similar to Power BI. You can use M Query in Power BI to query a multitude of data sources.
1 What is DAX?
DAX stands for Data Analysis Expressions. It is a language developed by Microsoft Company to interact with data in various platforms like Power BI, PowerPivot and SSAS tabular models. It is created and designed to be easy to learn and simple while highlighting the power and flexibility of tabular models. In a way, you could compare it with Excel formulas on steroids. Using DAX will genuinely unleash the capabilities of Power BI. For this reason, I chose to write this article on why you should use this tool out of the data science/data analysis toolbox.
2 Why Should We Learn DAX?
Power BI developers can use the Data Analysis Expressions (DAX) language to create visualizations with aggregations and complex logic. As a result, without learning DAX, one loses the ability to create out-of-the-box visualizations and reports that provide deeper insight than typical drag-and-drop reports. DAX is a spreadsheet, not a programming language. It is primarily a formula language that also functions as a query language. Custom calculations for Calculated Columns and Calculated Fields can be defined using DAX (also known as measures).
3 Top 6 Most Useful Functions
We will now look at DAX functions that any beginner learning Power BI DAX should be familiar with.
1. CALCULATE ()
Syntax: CALCULATE(<expression>, <filter1>, <filter2>…)
The CALCULATE function in Power BI is essential because it is used to set your own filters to the data, which can add to or even replace existing filters.
2. Filter ()
Syntax: Filter(<expression>,<filter1>,<filter2>…)
The filter function works similarly to CALCULATE; however, the FILTER functions are not mutable. So it can only subset the data. FILTER is an expression you can use in conjunction with an existing function such as CALCULATE the SUMX.
3. ALL Functions
Syntax: ALL(<table>[<column>])
The ALL function returns all of the rows in a table or column, regardless of any filters that have been applied. It simply ignores and clears all filters. This function comes in handy when we need to perform aggregations on all the rows in a table. This function is typically nested within CALCULATE or AVERAGEX.
4. X Functions
SUM ()
Syntax: SUM (Column Name)
The sum function adds the selected values in a column of the table.
SUMX ()
Syntax: SUMX (Table, Expression)
Assume you want to calculate sales revenue by dividing the order quantity by the unit price. This means you must create a column that computes [unit price] * [order Qty] and then add the resulting amount using SUM (). Using SUMX, this two-step process can be completed in a single step ().
AVERAGE ()
Syntax: AVERAGE (Column Name)
The average function returns the arithmetic mean value of all the numbers in a column.
AVERAGEX()
Syntax: AVERAGEX (Table Name, Expression)
Just like SUMX the AverageX calculates the average of an expression. So, this function saves extra steps of calculating the total and then taking the average.
MIÂÂÂÂÂN ()
Syntax: MIN (Column Name) or MIN (expression1, Expression2)
This function returns the smallest value between two scalar expressions or a column.
MAX()
Syntax: MAX(<column>) or MAX(<expression1>, <expression2>)
We use the MAX function to extract the maximum value from a column. It is the inverse of the MIN function in that it returns the maximum value between two scalar expressions or a column.
MINX () / MAXX ()
Syntax: MINX(<table>, < expression>) / MAXX(<table>, <expression>)
The MINX/MAXX function uses all of the DAX functions discussed above. The MAXX function returns the largest value after computing an expression for each row of a table. Similarly, the MINX function does the inverse, returning the smallest value.
COUNTX
Syntax: COUNTX (<table>,<expression> )
When evaluating an expression in a table, the COUNTX function does as its name implies. It counts the number of non-blank rows. This function does not work with boolean values.
5. Switch
Syntax: SWITCH ( <Expression>, <Value>, <Result> [, <Value>, <Result> [, … ] ] [, <Else>] )
SWITCH is a logical function that generates results based on multiple conditions. So, the Power BI switch function examines all logical conditions and returns the result of the logical condition: TRUE. SWITCH, unlike IF conditions, cannot perform complex calculations, but it is a good enough function to replace nested IF conditions in Excel.
6. Concatenate
Syntax: CONCATENATE ( <Text1>, <Text2> )
Join two text-based strings into a single text string.
- Text1 – The first text string being joined together to form a single text string. Strings can contain both text and numbers.
- Text2 – The second text string will be combined to form a single text string. Strings can contain both text and numbers.
- Return values (SCALAR) – A single-string value. The concatenated string.
If you want to concatenate multiple columns, you can perform a series of calculations or, better yet, by using the concatenation operator (&) to join them all in a single expression.
4 Frequently Asked Questions: DAX Functions
1. What are the most common DAX functions used?
- Date and Time Functions.
- Time Intelligence Functions.
- Information Functions.
- Logical Functions.
- Mathematical and Trigonometric Functions.
- Statistical Functions.
- Text Functions.
- Parent-Child functions.
2. What is the difference between SUMX and SUM in DAX?
SUMX is an iterator function with a unique approach. SUMX, in contrast to SUM, can perform row-by-row calculations and iterates through each row of a specified table to complete the calculation. SUMX then adds all the row-wise results of the given expression’s iterations.
3. What are DAX’s logical functions?
Logical functions operate on an expression to return an answer about the values or sets contained within it. You can, for example, use the IF function to check the outcome of an expression and generate conditional results
5 Conclusion
This article has introduced you to Power BI and DAX and highlighted their main features. It also reviewed the various types of useful DAX Functions available for Power BI. In addition, the article discussed why you should use the DAX Library when working with Power BI. After reading this article, you can try out the Power BI DAX Functions to improve your data analytics and visualization. The dashboard in Power BI dashboard is a single page, also known as a canvas, that uses visualizations to tell a story. You can use DAX to maximize its power.
Reach out for consultation on how to execute this for your business!
