Forum Discussion

gemcityzach's avatar
gemcityzach
Icon for Helper IV rankHelper IV
2 years ago

Summarizing 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 off...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
    gemcityzach
    Icon for Helper IV rankHelper IV
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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