cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bvilten
Helper II
Helper II

Combine rows based on column value

I am not sure exactly how to phrase this, which probably explains why I can't find a pre-existing answer.

Anyway I have a table that has multiple items which are repeated throughout by month and year. To cut down on visual clutter I want to somehow combine counts for the visual. I was thinking a measure would do for this but I'm not sure how to do the aggregation. Any help would be appreciated.

 

AD Reset318
VPN Lockout1285
AD Change PW44

 

What I want to show in the visulization is:

AD/Reset/Change/VPN Lockout1647

 

Thank you

1 ACCEPTED SOLUTION

Hi @bvilten,

 

You can try to use following formulas to extract item group and combine sub items.

 

Calculate column formulas:

Item Group = PATHITEM(SUBSTITUTE([Item],"/","|"),1)

Combined = 
CALCULATE (
    CONCATENATEX (
        VALUES ( Sheet1[Item] ),
        REPLACE (
            [Item],
            1,
            LEN ( PATHITEM ( SUBSTITUTE ( [Item], "/", "|" ), 1 ) ) + 1,
            ""
        ),
        "/"
    ),
    FILTER ( ALL ( Sheet1 ), [Item Group] = EARLIER ( Sheet1[Item Group] ) )
)

14.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

9 REPLIES 9
bvilten
Helper II
Helper II

Hello All,

Turns out I was making this way too complicated. So if you are looking for a non coded solution to aggregating rows based on a column value  try this. In either the Data or Report view click on ... to the right of column name you want to merge and select group. The next window will show you a listing of all unique entries in that column, CRTL click the entries you want combined then click group and name the group. Drop the group you created into a visualization and there you have it.

 

 

This worked great for my visual! Thank you!

MFelix
Super User
Super User

Hi @bvilten,

 

Create the following two measures:

Values total = SUM(Table1[Value])

summary type = CONCATENATEX(ALLSELECTED(Table1[Type]);Table1[Type];"/")

Then add them to your visual.

 

This will give you the result below, using the all selected allows you to add a slicer to select the type you have on your data, because the CONCATENATEX  needs a table so the ALLSELECTED creates that virtual table.

 

concatenx.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix,

 

Thank you for your response. It is close to what I am looking for, perhaps it is what I am looking for and I am not understanding. The table (Name: CombinedSDPData) below is a better representation of what I have. I already have year and month slicers. So I would want to concatonate the two Active Directory lines, ESS lines, and the two SAP lines as an example. When I create the specified measures I get errors as table1 not found. If I substitute CombinedSDPData for table1 I get items and counts but the counts ignore any filters for year and month. You also mention a virtual table and I am not sure what you mean by that. I am however digging into CONCANTONATE, and CONCANTONATEX functions so thank you for that lead as well

 

 

 

Snag_9f01de2.png

 

 

HI @bvilten,

 

You can't create dynamic calculate column/table based on slicer/filter, it can achieve this by measure but measure cannot used as axis fields.

 

In my opinion, I'd like to suggest you add two calculated columns to your table. One used to store category info of these records, other one used to store concatenate value based on category column.

 

After these steps, you can simply use 'category' , 'concatenate' columns as row field, 'year' 'month' as column field, 'amount' column as value field to create matrix visual.

 

If you are confused on coding formula, please share some sample data for test.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Confused doesn't begin to cover it 😉 Please see the following link for test.pbix

 

https://dartcontainer-my.sharepoint.com/:u:/p/d854142/ERPSHvquiulBkJjY8N2BYyoBZ6Ct8g_EqZDSLaKQwNSoVw...

Hi @bvilten,

 

You can try to use following formulas to extract item group and combine sub items.

 

Calculate column formulas:

Item Group = PATHITEM(SUBSTITUTE([Item],"/","|"),1)

Combined = 
CALCULATE (
    CONCATENATEX (
        VALUES ( Sheet1[Item] ),
        REPLACE (
            [Item],
            1,
            LEN ( PATHITEM ( SUBSTITUTE ( [Item], "/", "|" ), 1 ) ) + 1,
            ""
        ),
        "/"
    ),
    FILTER ( ALL ( Sheet1 ), [Item Group] = EARLIER ( Sheet1[Item Group] ) )
)

14.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you very much. I appreciate your help and your skill with Power BI. Is this DAX or M that you have given me?

HI @bvilten,

 

These are Dax formulas.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors