Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |