Help Adding a Concatenated Column to Matrix Visualization that Ignores Slicers

Question

Hello,I am working with data similar to the Sample Superstore. I’ve built a PBI matrix (attached) with several slicers, e.g., Sub-category, Product Name, etc. Please see the screenshot.&nbsp;I need to add a new column to the left of the Sub-category column titled “All items on order.” This column should display a concatenated list of all unique sub-categories associated with each order, e.g., "Binders, Chairs, Storage",&nbsp;and should ignore the Sub-category and Product Name slicers if they are selected.&nbsp;Here is a mock-up in Excel to illustrate the desired outcome:&nbsp;&nbsp;Could you please advise on how to implement this in Power BI? I was partially able to achieve it by putting the "All items on order" in the matrix values (very right column) but that's not an option for me. The column must be on the left of the Sub-category in the matrix rows.&nbsp;&nbsp;I couldn't upload the pbix sample file. Looks like it's no longer allowed here or something. Here is a link to my file on gdrive.Thank you!

nicanica07 · Answer

I couldn't upload the pbix sample file. Looks like it's no longer allowed here or something. Here is a link to my file on gdrive.

jihwan_kim · Answer

Hi, I am not sure if I understood your question correctly, but I tried to create a measure like below.
Please check the below picture and the attached pbix file.
&nbsp;

&nbsp;
All Sub Category list in the order: = 
VAR _orderid =
    SELECTEDVALUE ( Orders[Order ID] )
VAR _t =
    SUMMARIZE (
        FILTER ( ALL ( Orders ), Orders[Order ID] = _orderid ),
        Orders[Sub-Category]
    )
RETURN
    CONCATENATEX ( _t, Orders[Sub-Category], UNICHAR ( 10 ) )

ashish_mathur · Answer

Hi,
Write this measure
Measure = CONCATENATEX(CALCULATETABLE(VALUES(Orders[Sub-Category]),ALLEXCEPT(Orders,Orders[Order ID],Orders[Order Date],Orders[Country/Region],Orders[Segment])),Orders[Sub-Category],", ")
Hope this helps.

&nbsp;

nicanica07 · Answer

This does not work for my purposes, because as stated in the problem statement, the column must be to the left of the Sub-category and not all the way on the right. It has to be added as a row to the matrix, not as a value. But thank you so much for trying to help!

nicanica07 · Answer

Thank you for taking the time to help me! This is not what I am looking for because the table shows duplicated orders instead of one order per row.&nbsp;

Forum Discussion

Help Adding a Concatenated Column to Matrix Visualization that Ignores Slicers

7 Replies

Related content

CONCATENATE BUT IGNORE BLANKS

Table Visuals Concatenate

Concatenate lists ;

concatenate strings

Group and Concatenate