Summarizing concatenated values in a field

[gemcityzach]

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

[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.

[Anonymous]

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

[Greg_Deckler]

@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

[gemcityzach]

I 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?

[Anonymous]

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