CONCATENATEX() in Power BI (With Step-by-Step Example)

Posted by

September 17, 2026

On March 15, 2026

Introduction

CONCATENATEX in Power BI is a powerful DAX function that allows you to concatenate text values from multiple rows into a single string, while it still respects filters, sorting, and report context. CONCATENATEX iterates over a table, giving you precise control over how values are joined and displayed.

In this blog post, we will break down CONCATENATEX in Power BI with a practical example to show how it works.

What is CONCATENATEX in Power BI ?

CONCATENATEX is a DAX iterator function that, in particular, combines text values from several rows into a single text string using a delimiter.

TableName: The table in the model, or a virtual table created by tabular functions, or coming from a variable.
Expression: What expression from the given table should be concatenated.
Delimiter: The delimiter to use when concatenating values.

Learn more about Iterators in Power BI in our blog: Basic Iterators in DAX: SUMX()

In Practice, the best way to understand CONCATENATEX is to go through an example.

Step-by-step Tutorial

For this tutorial, find the exercise file provided at the end of this blogpost. In the file, you will find the maximum daily sales. Each day can have multiple sales, and there can be many days with the same maximum sales.

To find the dates with the maximum daily sales:

1. First, right-click on the All Measures table in the Fields pane.

2. Click on New measure.

Measure tools open.

3. Here, type the following formula in the formula bar and press Enter.

Max Daily Sales (CM) =
MAXX ( ‘Date’, [Total Sales Amount (CM)] )

4. Click on the “$” in the formatting section.

5. Now, type “2” in the text box.

6. Then, right-click on the All Measures table in the Fields pan.

7. Click on New measure.

Measure tools open.

8. Now, type the following formula in the formula bar and press Enter.

Date of Max Sales (CM) =
VAR MaxDailySales = [Max Daily Sales (CM)]
VAR DatesWithMaxSales =
FILTER ( ‘Date’, [Total Sales Amount (CM)] = MaxDailySales )
VAR Result =
CONCATENATEX ( DatesWithMaxSales, FORMAT ( ‘Date'[Date], “mm/dd/yy” ), “, ” )
RETURN
IF ( NOT ISBLANK ( MaxDailySales ), Result, BLANK () )

In the expression above:

MaxDailySales finds the maximum daily sales using the measure.
DatesWithMaxSales finds the date when the maximum sales were recorded.
CONCATENATEX() has been used to concatenate the list of dates containing highest sales in the current filter context, after evaluation.

9. To visualise, click on the matrix visual in the visualization pane.

10. Here, drag and drop Year, Qtr, Full Month, and Date from the Date table to the rows.

A hierarchy will be detected by Power BI.

11. Now, drag and drop Total Sales Amount (CM), Max Daily Sales (CM), and Date of Max Sales (CM) from the All Measures table to the Values.

12. The following will be our output Matrix.

Want to learn the difference between a table and a matrix? Check out our blog: Power BI Table vs Matrix: Which One Works Best for Your Data?

Result Analysis

The matrix above shows Total sales, Max Daily Sales and the date of Max Sales at different time levels. As the hierarchy expands, the measures recalculate based on the current filter context, so that each row will display the maximum daily sales only for the visible dates at that specific level.

CONCATENATEX is used here to return the date of maximum sale safely, since the highest sales value can occur on multiple dates. Rather than causing an error or giving a single arbitrary date, CONCATENATEX here combines all matching dates into a single readable result, which ensures the accuracy and reliability of output across all hierarchy levels.

Conclusion

CONCATENATEX in Power BI is a powerful DAX function, that makes it easy to combine text values from several rows into one output that is easy to read. By iterating over a table and respecting filter and report context, it provides flexible control over how text results are generated and displayed.

CONCATENATEX makes sure that the results are always correct when a single value applies to more than one row, like dates, categories, or labels. Using CONCATENATEX correctly, makes visuals clearer, insights more reliable, and Power BI reports better designed.

FAQs

What is CONCATENATEX used for in Power BI

CONCATENATEX is used to merge text values from several rows into a single text string while maintaining filter context. It is frequently used in tables, cards, tooltips, and summary graphics.

How is CONCATENATEX different from CONCATENATE

CONCATENATEX iterates over a table and can combine values from multiple rows, on the other hand, CONCATENATE only joins two text values. CONCATENATEX is more adaptable and appropriate for practical reporting situations.

Does CONCATENATEX respect filters and slicers

Yes. Slicers, visual-level filters, and row context from visuals like matrices and tables are all fully respected by CONCATENATEX.

Further Learning and Resources

To learn more about Power BI, check out our courses here:

Want to learn how Microsoft 365 Copilot transforms everyday work with AI? Check out our Course:

Microsoft 365 Copilot Training Courses

Want to learn more about Power BI and how to make reports more efficiently?

Power BI training in Australia: We offer comprehensive training sessions across various cities, including :

Power BI Crash Course eBook: Download our detailed guide to take your Power BI expertise to the next level, covering topics from basics to advanced with practical examples and step-by-step instructions. (Limited time offer)

Download the Beginner’s Guide to Power BI

Ready to enhance your Power BI skills? Sign up for our training courses and explore our wide range of resources designed to elevate your data analytics capabilities.