Double concatenate(x) with group by

[WouterV]

Dear Community,

I have a report with a dataset in which I'm trying to group values (by concatenating) on 2 levels.

Imagine below dummy dataset and keep in mind that ColX would be in reality around 20 columns in the report.

ColX	VehicleType	Brand
1	SUV	Mercedes
1	Family	Ford
2	Convertible	Porsche
2	Convertible	Volkswagen
3	Family	Ford
3	SUV	Ford
4	SUV	Skoda
4	SUV	Toyota
4	Family	Skoda
4	Family	Toyota

Should merge to

ColX	VehicleType	Brand
1	SUV	Mercedes
1	Family	Ford
2	Convertible	Porsche, Volkswagen
3	Family, SUV	Ford
4	SUV, Family	Skoda, Toyota

So, basically, case 1 should stay split and the rest should merge as they have overlap either on VehicleType or Brand, or both.

Using CONCATENATEX on both columns works for cases 2, 3, 4, but it's also merging case 1 into 1 record, which I don't want.

I also tried using some helper tables via SUMMARIZE, one for "VehicleType", one for "Brand" and then trying to put the things together again, but no avail.

Any  suggestions on possible solutions that might work?

Again, remember that "ColX" from the example is actually around 20 columns of different properties of my dataset, so preferably a suggestion that doesn't require putting all these 20 columns in a DAX function, but something that leverages the grouping of the matrix.

Thanks.

[lbendlin · Accepted solution]

Ok, there's a lot to unpack here.  First my proposed solution. Disclaimer: This is based on Calculated Tables. I don't think you can achieve this with measures.

First, some helper calculated columns in the main table:

CtR = CALCULATE(COUNTROWS(Groups),ALLEXCEPT(Groups,Groups[ColX]))
CtB = CALCULATE(DISTINCTCOUNT(Groups[Brand]),ALLEXCEPT(Groups,Groups[ColX]))
CtV = CALCULATE(DISTINCTCOUNT(Groups[VehicleType]),ALLEXCEPT(Groups,Groups[ColX]))

Next we identify the rows that need to stay unique and put them into a calculated table

Unique = FILTER(Groups,Groups[CtV]=Groups[CtR] && Groups[CtV]=Groups[CtB])

Then we concatenate what can be grouped into another calculated table

Groupable = SUMMARIZE(filter(Groups,Groups[CtV]<>Groups[CtR] || Groups[CtV]<>Groups[CtB]),Groups[ColX],"VehicleType",CONCATENATEX(Values(Groups[VehicleType]),Groups[VehicleType],","),"Brand",CONCATENATEX(values(Groups[Brand]),Groups[Brand],","),"CtR",MAX(Groups[CtR]),"CtB",max(Groups[CtB]),"CtV",max(Groups[CtV]))

And lastly we append both calculated tables for the final result

Combined = UNION(Unique,Groupable)

This will still require some cleanup but it works on the sample dataset.

Note: Please don't use the term "columns"  when you mean something else.

[WouterV]

Solution works for me, thanks!

Too bad it requires stating all my group fields (the "ColX" corresponding to 20+ fields in my real dataset) in multiple measures and calculated tables. Will be tough on maintenance, but at least I have a solution now and the client can be happy 🙂