Welcome to the world of data visualisation and analysis with Power BI! In this blog, we’ll dive into the heart of Power BI’s analytical capabilities: DAX measures.

DAX, or Data Analysis eXpressions, is a powerful formula language that enables users to define custom calculations in Power BI reports. Whether you’re a seasoned data analyst or just starting out, understanding DAX measures is key to unlocking insightful business intelligence.

So, let’s explore some of the most popular measures, array functions, and time-intelligence functions, and see how they can transform raw data into meaningful insights.

Power BI – What is DAX? — Calculate Measure

Some often-used Power BI DAX measures include:

SUM: calculates the sum of a selected column of data
AVERAGE: calculates the average of a selected column of data
COUNT: counts the number of rows in a selected column of data
MIN/MAX: calculates the minimum or maximum value in a selected column of data
DISTINCTCOUNT: counts the number of unique values in a selected column of data
CALCULATE: modifies the context within which a DAX formula is evaluated
FILTER: filters a table based on specific criteria
RANKX: ranks values in a selected column of data
YEAR/QUARTER/MONTH: extracts various time periods from a date column in a table

DAX measures allow users to perform complex calculations and analysis on their data quickly and effectively, providing valuable insights into trends and patterns in their data.

Array functions

Many measures in Power BI desktop need to be array measure, because of the data model structure in a Power BI model.

One of the powerful features in DAX is this ability to create array measures, which are measures that return an array of values instead of a single value. This can also be complicated for users, who haven’t been working with data cubs.

Here are some examples of DAX array measures:

1. DISTINCTCOUNT function:

DISTINCTCOUNT returns the number of distinct values in a column or expression. When used in an array formula, it returns a list of distinct counts for each value in another column.

For example, the following DAX formula returns an array of distinct count values for the “Product” column:

= DISTINCTCOUNT(Products[Product])

2. FILTER function:

FILTER returns a table that meets certain criteria specified in the expression. When used in an array formula, it returns a list of filtered tables for each value in another column.

For example, the following DAX formula returns an array of filtered tables for each value in the “Category” column:

= FILTER(Products, Products[Category] = EARLIER(Products[Category]))

3. VALUES function:

VALUES returns a table of unique values in a column or expression. When used in an array formula, it returns a list of unique values for each value in another column.

For example, the following DAX formula returns an array of unique values for each value in the “Category” column:

= VALUES(Products[Category])

4. SUMX function:

SUMX returns the sum of an expression for each row of a table. When used in an array formula, it returns a list of sum values for each value in another column.

For example, the following DAX formula returns an array of sum values for each value in the “Product” column:

= SUMX(Products, Products[Price])

Array measures are a powerful way to analyse data and gain insights from multiple dimensions at once. With DAX, you can build complex array formulas that can handle large amounts of data and answer specific business questions.

1. Sum of Sales by Year:

SUMX(

GROUPBY(Sales, Sales[Year], “SalesByYear”, SUM(Sales[Amount])),

SalesByYear

)

This formula groups sales by year and calculates the sum of sales amount for each year.

2. Average Sales per Customer:

AVERAGEX(

GROUPBY(Sales, Sales[CustomerID], “SalesByCustomer”, SUM(Sales[Amount])),

SalesByCustomer

)

This formula groups sales by customer ID and calculates the sum of sales amount for each customer. Then, it takes the average of all the customer sales.

3. Last Sale Date for Each Customer:

MAXX(

GROUPBY(Sales, Sales[CustomerID], “LastSaleByCustomer”, MAX(Sales[Date])),

LastSaleByCustomer

)

This formula groups sales by customer ID and finds the maximum date of sales for each customer, which represents the last sale date for that customer.

4. Sales Growth Rate by Month:

DIVIDE(

SUM(Sales[Amount]),

CALCULATE(SUM(Sales[Amount]), DATEADD(Sales[Date], -1, MONTH)),

BLANK()

) – 1

This formula calculates the growth rate of sales from the previous month. It uses the DIVIDE function to divide the total sales amount by the total sales amount from the previous month. Then, it subtracts 1 to get the growth rate percentage.

5. Running Total of Sales by Month:

CALCULATE(

SUM(Sales[Amount]),

FILTER(

ALL(Sales),

Sales[Date] <= MAX(Sales[Date])

)

This formula calculates the running total of sales by month. It uses the CALCULATE function to add up the sales amount for all dates that are less than or equal to the maximum date in the current filter context. The FILTER function is used to remove any filters on the date column that might interfere with the running total calculation.

Time-intelligence functions

DAX (Data Analysis Expressions) Time-intelligence functions allow users to analyse data over time, providing insights into key trends and patterns.

Some common DAX Time-intelligence functions include:

TOTALYTD: This function returns the total value of a given measure from the beginning of the year up to the selected date.
TOTALQTD: This function returns the total value of a given measure from the beginning of the quarter up to the selected date.
TOTALMTD: This function returns the total value of a given measure from the beginning of the month up to the selected date.
SAMEPERIODLASTYEAR: This function returns the total value of a given measure for the same period last year.
DATESYTD: This function generates a table with all the dates within the current year up to the selected date.
DATEQTD: This function generates a table with all the dates within the current quarter up to the selected date.
DATEADD: This function adds a specified number of units to a given date.

Example: DATEADD Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates, in the current context.

Syntax: =DATEADD(<Dates>,<Number of intervals>,<Interval>)

where the <Interval> can either be Year, Month, Quarter or Day

These functions can be used in combination with other DAX functions to create more complex analyses and visualisations.

Conclusion

As we wrap up our exploration of DAX measures in Power BI, it’s clear that these tools are indispensable for any data professional. From calculating simple sums to performing complex time-based analyses, DAX measures empower us to make data-driven decisions with confidence.

We encourage you to experiment with the examples provided and discover the full potential of DAX in your own business scenarios. Remember, the power of data is at your fingertips, and with Power BI and DAX, there’s no limit to the insights you can uncover!

When you click inside the text box a tool bar will appear. Click +VALUE and you can type in the DAX measure you want to display the result from. Here I want to display the result from a DAX measure called sales. I have formatted the result as currency, and I named the result “totsales.” The name can be used to display the result somewhere else in the text box.

In the same text box, you may want to explain to the audience, how the visual and the information have been filtered. In this example a slicer has been added to page to filter the data by country.

The following DAX measure will pick up if the column with the heading Country in the table “tblsales” is filtered. If it is, it will return the country name selected from the slicer. Otherwise, it will return the text string ” all countries”.

Filtered countries = if(ISFILTERED(tblsales[Country]),SELECTEDVALUE(tblsales[Country]),”all countries”)

If you look at the screenshot above, you can see that the total sales amount is added to the text box and a new line at the top has been typed:
“This page visuals and the information in this text box display data from”

Place the mouse course where you want to add the dynamic text and click “+Value” and again the options will appear.

Now the text will show this if the page has not been filtered by country:

And this if the page has been filtered by “UK”:

Example 2 – Combination of Q&A artificial intelligence and your own DAX measures and text.

If you do not want the smart narrative dynamic text, you can use the same method as in example 1 above. You will then just need to add a text box to the page.

In this example you will also see how the artificial intelligence tool Q&A can benefit you when you are creating dynamic text.

If we type “The percentage sales margin for” in a text box, then as in example 1 the DAX measure Filtered countries is used to return the country selected from the slicer. Then “is” is typed and then as in example 1, where a measure called Sales was added, another measure is added where the percentage sales margin was calculated. The same has been done to get the result from a profit measure.

At this point, the text box displays:

The percentage sales margin for Canada is 49.19 %,

the profit for Canada is £269,079,481,

and the total quantity for Canada is

You will not even always have to do measures to return calculated results in dynamic text. Here the artificial intelligence tool Q&A can be particularly useful.

To get the total quantity in the text box click “+Value.”

This will look in your data set and find a column with the name quantity and sum all the values in the column.

You will need to know your column headers.

“top 1 ClientName profit” Q&A will look in the column ClientName and find top 1 client’s value based on a measure called profit.

And the result will look like this:

And this:

Will return the name of top 1 client based on profit.

Example 3 – Dynamic text in custom Tooltips for more detailed reports.

The methods are the same as above! The only difference is that the text box needs to be created on a tooltip page.

The dynamic text is also filtered by the data point you hover over on the visual. In the screen shot above, the mouse courser is on top of the data point for August 2019.

Conclusion

You can add much more information to your reports by creating dynamic text boxes. They enable you to explain the visuals, you can also point out specific details, which are important for the audience.

Filtering can be confusing in a Power BI report for the audience, but as you saw in this blog post, you can explain filters in dynamic text.

Array functions

1. DISTINCTCOUNT function:

2. FILTER function:

3. VALUES function:

4. SUMX function:

1. Sum of Sales by Year:

2. Average Sales per Customer:

3. Last Sale Date for Each Customer:

4. Sales Growth Rate by Month:

5. Running Total of Sales by Month:

Time-intelligence functions

Conclusion

Further Reading

DAX

Power BI Report Visuals

Example 1 – Combination of Smart Narrative and your own DAX measures and text.

Example 2 – Combination of Q&A artificial intelligence and your own DAX measures and text.

Example 3 – Dynamic text in custom Tooltips for more detailed reports.

Conclusion

Further Reading: