Consolidated list of products

Imagine this scenario: you register customers in an Excel spreadsheet, specifying their ID and then the products they use. Which customer uses which products? One way to get this answer is to create a pivot table, and then display customers, products and the number of products. The disadvantage of this report is that pivot tables always aggregate data. What if we only need a table with customer ID in one column and a consolidated list of products in another?

CONCATENATEX function

The CONCATENATE function is used to join strings in the DAX language. DAX also has a corresponding “X” function that acts as an iterator and allows joining of multiple strings with an option to use delimiter and sorting direction by given expression. In this post, you will learn how to use the CONCATENATEX function in a specific example, by displaying all brands in relation to the filter of the manufacturer to which they belong.