Forum Discussion
Helper IVSummarizing concatenated values in a field
Hey folks,
I'm trying to figure out if there is an easy way to summarize concatenated values in a field, without having to split the fields into rows. But if that's the only way, then can you offer some suggestions for how to manage the fact that I have multiple fields that I'd like to perform these kinds of calculations on? Essentially, I have work units that can be assigned to multiple groups (1:n). I.e., one work unit could be assigned to up to 10 groups. I'd like to be able to show count data on how many units are being worked on. This same theme I'd like to be able to report on other concanenated fields.
To date what I've been doing is created a refeference of my Main query and then splitting the column to add new rows by Group. This allows me to get counts by Group. But for each Feature I want to create unique counts on, I have to generate another reference query to split on that Feature. Essentially, I'm trying to not have to create duplicate rows for every concatenation.
| ID | Group | Tag1 | Product | Created |
| RD-2024-00001 | Group1;Group2;Group3 | Blue;Red;Green | Guitar | 04/04/2024 |
| RD-2024-00002 | Group4;Group3;Group1 | Orange;Green;Red | Stompbox | 04/04/2024 |
| RD-2024-00003 | Group2;Group3;Group5 | Purple;Red;Green;Blue | Strings | 04/04/2024 |
Is there a way to get summarized values by Group of the form, without having to split the field into rows?
| Group | Count_of_ID |
| Group1 | 2 |
| Group2 | 2 |
| Group3 | 2 |
| Group4 | 1 |
| Group5 | 1 |
| Group | Red |
| Group1 | 3 |
| Group2 | 2 |
| Group3 | 3 |
| Group4 | 1 |
| Group5 | 1 |
6 Replies
- Greg_Deckler
Community Champion
gemcityzach If you have your groups in a separate disconnected table, maybe:
Measure Count of ID = VAR __Group = MAX('Groups'[Group]) VAR __Table = DISTINCT( SELECTCOLUMNS( FILTER( 'Table', FIND( __Group, [Group],, -1) <> -1 ) ) ), "__ID", [ID] ) VAR __Result = COUNTROWS( __Table ) RETURN __Result
gemcityzachI think I could create a table in the data model using the Group Names. Like a Reference Table that drops everything except Group Name and then removes all duplicates. You think I could reference to that?
Hi gemcityzach,
Here are Dax expressions about extract and expand the raw table field value to create new calculated tables and measure expressions.
Then you can use these field to create table visual and measure expression to calculate the count based on current field value.calculated tables:
DimGroup = VAR _path = SUBSTITUTE ( CONCATENATEX ( VALUES ( 'T1'[Group] ), [Group], ";" ), ";", "|" ) VAR _length = PATHLENGTH ( _path ) VAR _pathtable = ADDCOLUMNS ( GENERATESERIES ( 1, _length, 1 ), "Group", PATHITEM ( _path, [Value] ) ) RETURN DISTINCT ( SELECTCOLUMNS ( _pathtable, "Group", [Group] ) ) DimTag = VAR _path = SUBSTITUTE ( CONCATENATEX ( VALUES ( 'T1'[Tag1] ), [Tag1], ";" ), ";", "|" ) VAR _length = PATHLENGTH ( _path ) VAR _pathtable = ADDCOLUMNS ( GENERATESERIES ( 1, _length, 1 ), "Tag", PATHITEM ( _path, [Value] ) ) RETURN DISTINCT ( SELECTCOLUMNS ( _pathtable, "Tag", [Tag] ) )Measures:
GroupCount = VAR currGroup = SELECTEDVALUE ( DimGroup[Group] ) RETURN COUNTROWS ( FILTER ( ALLSELECTED ( T1 ), SEARCH ( currGroup, [Group],, 0 ) > 0 ) ) TagCount = VAR currTag = SELECTEDVALUE ( DimTag[Tag] ) RETURN COUNTROWS ( FILTER ( ALLSELECTED ( T1 ), SEARCH ( currTag, [Tag1],, 0 ) > 0 ) )
Regards,Xiaoxin Sheng
- gemcityzach
Hi Xiaoxin, thanks for this! It's really interesting and I'm working on how to integrate it. What I can't figure out is whether or not it's possible to get the aggregations of Tags by Group? Like in my second table.
The Tags calculated table is currently returning a List, which is the same as the original data source. So the measure is only returning a total count of tags.
HI gemcityzach,
You can add two measure formulas to get the aggregated tag count based on current group, here are the formulas:
TagCount By Group = VAR currGroup = SELECTEDVALUE ( DimGroup[Group] ) VAR TagList = CALCULATE ( CONCATENATEX ( VALUES ( T1[Tag1] ), [Tag1], ";" ), FILTER ( ALLSELECTED ( T1 ), SEARCH ( currGroup, [Group],, 0 ) > 0 ) ) RETURN PATHLENGTH ( SUBSTITUTE ( TagList, ";", "|" ) ) Distinct TagCount By Group = VAR currGroup = SELECTEDVALUE ( DimGroup[Group] ) VAR tagPath = CALCULATE ( SUBSTITUTE ( CONCATENATEX ( VALUES ( T1[Tag1] ), [Tag1], ";" ), ";", "|" ), FILTER ( ALLSELECTED ( T1 ), SEARCH ( currGroup, [Group],, 0 ) > 0 ) ) VAR _pathtable = ADDCOLUMNS ( GENERATESERIES ( 1, PATHLENGTH ( tagPath ), 1 ), "Tag", PATHITEM ( tagPath, [Value] ) ) RETURN COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( _pathtable, "Tag", [Tag] ) ) )Regards,
Xiaoxin Sheng
- gemcityzach
Thanks for your help. What I meant is that I should be able to summarize in some way, each tag by each group. I want to be able to show total observations by Group and also Tag Observations by Group.
Tag Observations by Group
Group Tag Count Group1 Red 1 Group1 Blue 3 Group1 Green 15 Group2 Black 56 Group2 Red 2 Group2 Blue 6 Group3 etc etc etc etc etc Total Observations by Group
Group Count Group1 19 Group2 64 Group3 etc
