Help Adding a Concatenated Column to Matrix Visualization that Ignores Slicers

[nicanica07]

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.

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", and should ignore the Sub-category and Product Name slicers if they are selected.

Here is a mock-up in Excel to illustrate the desired outcome:

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.  

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!

[v-saisrao-msft]

Hi @nicanica07,

Thank you @Ashish_Mathur,@Jihwan_Kim , for your insights.

If the issue still persists on your end, we recommend reaching out to our Power BI certified solution partners. These are highly experienced professionals who can provide in-depth technical assistance and offer tailored solutions based on your specific needs. You can explore the list of trusted partners here:

Power BI Partners | Microsoft Power Platform

You’re always welcome to continue engaging with the community as well,
Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread.
we truly appreciate your active participation in the Microsoft Fabric Community.

Thank you.

[Ashish_Mathur]

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.

[nicanica07]

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!

[Jihwan_Kim]

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.

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 ) )

[nicanica07]

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. 

[nicanica07]

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.