how to aggregate on a column that contains combination of values

[ukeasyproj]

What I want to do is aggregate on a person's name and show all their projects:

Name | Project ID

--------

John   | 1

--------

Mike   | 2

--------

Mike, John | 3

--------

David | 4

so

for John, it should show project 1 as well as 3

For Mike, it shoulld show project 2 as well as 3

[Vvelarde · Accepted solution]

hi @ukeasyproj

please follow this steps

1. your original data

Original data

2. in Edit Query Split column

By delimeter , (Comma)

3. Unpivot Columns (Selecting ProjectID)

UnPivot Columns

4. Remove Attribute Column....Close & Apply

5. Create a measure to concatenate the projectid column

[Sean]

You can do this in The Query Editor - just follow the Steps in the picture...

In your case your ProjectID column should be the Unique Row Identifier

And your Name column the Slicer column

I believe there was another solution listed here but I have never tried the approach...

http://community.powerbi.com/t5/Desktop/implementing-where-on-slicer/td-p/43446