Home » CONCATENATEX Function DAX

CONCATENATEX Function DAX

CONCATENATEX function is a Power Bi text function in DAX, which evaluates expression for each row on the table, then return the concatenation of those values in a single string result, separated by the specified delimiter.




DAX SYNTAX

CONCATENATEX(<table>, <expression>, [delimiter])

table           is a table containing the rows for which the expression will be evaluated.
expression is expression to be evaluated for each row of the table.
delimiter    a separator to use during concatenation. It is an optional.

 

Lets look at an example of using CONCATENATEX function in Power Bi, Here we sample data that represent the Total Quantity by Category and Subcategory.

 

As you can see, for any category there are multiple sub categories. If you wish to see all the subcategories realted to any category into single string, separated by the specified delimiter then you can do this using CONCATENATEX function.

Lets create a measure- SubCategoryList which uses CONCATENATEX function which concatenates the subcategories into single string, by spearating comma (,).

 

SubCategoryList =
CONCATENATEX (
VALUES ( 'Global-Superstore'[Sub-Category] ),
'Global-Superstore'[Sub-Category],
", "
)

 

Once you commit the DAX, Lets drag this on table visual, and you can see for each category all subcategories are concatenated into single string lists,  separated by comma.

If  you notice, measure is retuning all the unique subcategories for all categories in TOTAL also.

If you do not want to display it on TOTAL, then you remove it by modifying DAX as shown below.

SubCategoryList =
IF (HASONEVALUE('Global-Superstore'[Category]),
CONCATENATEX (
VALUES ( 'Global-Superstore'[Sub-Category] ),
'Global-Superstore'[Sub-Category],
", "
), "")

 

Now you can see, all concatenated subcategories string are removed from TOTAL

You can also add multiple expression in CONCATENATEX function, suppose you want to add quantity for each subcategory in string.

Lets modify the DAX, to display quantity along with each subcategory in string.




SubCategoryList =
IF (HASONEVALUE('Global-Superstore'[Category]),
CONCATENATEX (
VALUES ( 'Global-Superstore'[Sub-Category] ),
'Global-Superstore'[Sub-Category] & " :" & SUM('Global-Superstore'[Quantity]),
", "
), "")

Now you can see that there is a quantity along with subcategory in string.

 

 

 

 

Power Bi DAX




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 1,421 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.