Concatenate Rows based on Column Value to be used in X-Axis on Graphs

[andrewmshoe]

New to Power BI, so bear with me please... I have looked extensively for a solution, and have gotten close, but not quite there.

I need to allow an end user to manipulate a data column based on input from a slicer.  Here's some dummy input data:

 

I load the data in Power BI and unpivot the category columns to get the following:

 

Now, I want the user to be able to check which categories they are interested in getting calculated fields such as AVG Selling Price, AVG Customer Rating, or product count by configuration.

I am jumping to edit queries to help visualize my thought process with the slicer:

Next I would want to concatenate the rows for each Unique Identifier (i.e. #1 = Cube Small) into an existing additional column.  This concatenated column would be the x-axis for bar graphs, legends for scatter plots, etc...

Any ideas?  I created a new measure and used the Concatenatex function, which then could be used in a matrix, but that isn't robust enough.

[andrewmshoe]

Thank you both for the replies.  Sorry, for not being quite clear enough.

Let's pretend there is an engineering team for the size and shape of the widget we sell.  They want to know all of the different configurations of sizes and shapes ordered, how much they each cost on average, and how satisfied the customers are for each configuration.  Here's a sample of a graph they would want:

They don't care about the color or weight so they don't want those to be included in defining all the possible configurations.  Well, another team cares about the shape, color, and weight and all the permutations that exist there.  Same story in terms of requested outputs, but different categories to concatenate.

This could be done very easily if the requestors were able to edit the dashboard and create a new column concatenating the category columns they are interested in:

At this point they could use "NEW COLUMN" as the x-axis in graphs or whatever.  However, this will eventually be published online so they won't have the ability to self-service the tool in this way.  I need a way that allows them to dynamically modify a column based on a slicer.

This is where the strategy of unpivoting comes into play.  Now the Categories are in one column and a slicer can be used to filter the data.  To get the equivalent of the "NEW COLUMN" with this strategy, I need to concatenate the value column for each unique identifier.  

So if the engineering team slices the data like this:

The NEW COLUMN would get calculated to be this:

Then I would use the NEW COLUMN as the x-axis/legend/etc... for my visuals.

[v-frfei-msft]

Hi @andrewmshoe,

I create a calculated to achieve your goal.

Column = CALCULATE(CONCATENATEX(Table1,Table1[value], " "),ALLEXCEPT(Table1,Table1[id]))

For more details, please check the pbix as attached.

Regards,

Frank

[andrewmshoe]

Hi Frank,

Thank you for the reply.  I have been away from my computer for a few days so I apologize for the slow response.

I tried your solution and it appears to be static instead of dynamic which I require.  See below:

I make a bar graph to show average selling price by "Column"

 

At this point I create a filter on "Attribute" but the bar graph does not update:

I need the "Column" column to dynamically update based on the slicer selection.  Do you know of a way to do this?

[andrewmshoe]

I would like to attach my dummy data file shown in the previous post, but I do not see an option to upload the file. I am assuming my forum rank prohibits attaching anything.  

I will not be able to use dropbox/one drive to send a link to the upload either.

[v-frfei-msft]

Hi @andrewmshoe,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[v-frfei-msft]

Hi @andrewmshoe,

Could the formula be used for you? If not, kindly share your excepted result to me.

Column = CONCATENATE(yourtable[Unique Identifier],yourtable[Attribute])

Regards,

Frank

[Greg_Deckler]

Can you show an example of what you are trying to achieve. I didn't get that from your message.