Forum Discussion

andrewmshoe's avatar
andrewmshoe
Frequent Visitor
8 years ago

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

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 i...
andrewmshoe's avatar
andrewmshoe
Frequent Visitor
8 years ago

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's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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

    • v-frfei-msft's avatar
      v-frfei-msft
      Icon for Community Support rankCommunity Support
      8 years ago

      Hi andrewmshoe,

       

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

       

      Regards,
      Frank

    • andrewmshoe's avatar
      andrewmshoe
      Frequent Visitor
      8 years ago

      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's avatar
        andrewmshoe
        Frequent Visitor
        8 years ago

        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.