Count repeated values in a row

Hi All,

I have column in data where values are repeating. Can you please suggest me DAX measure or column by which I can Calculate the the number of times a value is repeating in a row.

Customer ID | Product Path | Expected Result

1 A,A,B,B,B,B,B,C,C,C 2-A, 5-B, 3-C

2 G,H,A,B,B 1-G, 1-H, 1-A, 2-B

Thank you so much for any given help.

Chs.

tamerj1

4 years ago

Hi Anonymous
Here is a sample file with the solution https://www.dropbox.com/t/tbRUhGvyoNDAZ5xL
I believe a fount a solution for your problem. It is a long code but it works just fine for upto 15 products per row. It can be expanded further if needed.
Please check and let me know if it works with you.
Here is how the table and report look like

And here is the code of the calculated column

Products = 
VAR Table1 = 
    SELECTCOLUMNS ( { ( [Customer ID], [Product Path] ) }, "Customer ID", [Customer ID], "Product Path", [Product Path] )
VAR Table2 =
    GENERATE ( 
        Table1,
        VAR Length = LEN ( Store[Product Path] )
        VAR Loc1 = IFERROR ( FIND ( ",", Store[Product Path], 1 ), 0 )
        VAR Loc2 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc1 ), 0 )
        VAR Loc3 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc2 ), 0 )
        VAR Loc4 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc3 ), 0 )
        VAR Loc5 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc4 ), 0 )
        VAR Loc6 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc5 ), 0 )
        VAR Loc7 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc6 ), 0 )
        VAR Loc8 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc7 ), 0 )
        VAR Loc9 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc8 ), 0 )
        VAR Loc10 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc9 ), 0 )
        VAR Loc11 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc10 ), 0 )
        VAR Loc12 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc11 ), 0 )
        VAR Loc13 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc12 ), 0 )
        VAR Loc14 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc13 ), 0 )
        VAR Loc15 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc14 ), 0 )
        VAR P1 = IF ( Loc1 = 0, Store[Product Path], MID ( Store[Product Path], 1 , Loc1 - 1 ) )
        VAR P2 = IF ( Loc2 = 0, IF ( Loc1 <> 0, MID ( Store[Product Path], 1 + Loc1, Length - Loc1 ) ), MID ( Store[Product Path], 1 + Loc1, Loc2 - Loc1 - 1 ) )
        VAR P3 = IF ( Loc3 = 0, IF ( Loc2 <> 0, MID ( Store[Product Path], 1 + Loc2, Length - Loc2 ) ), MID ( Store[Product Path], 1 + Loc2, Loc3 - Loc2 - 1 ) )
        VAR P4 = IF ( Loc4 = 0, IF ( Loc3 <> 0, MID ( Store[Product Path], 1 + Loc3, Length - Loc3 ) ), MID ( Store[Product Path], 1 + Loc3, Loc4 - Loc3 - 1 ) )
        VAR P5 = IF ( Loc5 = 0, IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc4, Length - Loc4 ) ), MID ( Store[Product Path], 1 + Loc4, Loc5 - Loc4 - 1 ) )
        VAR P6 = IF ( Loc6 = 0, IF ( Loc5 <> 0, MID ( Store[Product Path], 1 + Loc5, Length - Loc5 ) ), MID ( Store[Product Path], 1 + Loc5, Loc6 - Loc5 - 1 ) )
        VAR P7 = IF ( Loc7 = 0, IF ( Loc6 <> 0, MID ( Store[Product Path], 1 + Loc6, Length - Loc6 ) ), MID ( Store[Product Path], 1 + Loc6, Loc7 - Loc6 - 1 ) )
        VAR P8 = IF ( Loc8 = 0, IF ( Loc7 <> 0, MID ( Store[Product Path], 1 + Loc7, Length - Loc7 ) ), MID ( Store[Product Path], 1 + Loc7, Loc8 - Loc7 - 1 ) )
        VAR P9 = IF ( Loc9 = 0, IF ( Loc8 <> 0, MID ( Store[Product Path], 1 + Loc8, Length - Loc8 ) ), MID ( Store[Product Path], 1 + Loc8, Loc9 - Loc8 - 1 ) )
        VAR P10 = IF ( Loc10 = 0, IF ( Loc9 <> 0, MID ( Store[Product Path], 1 + Loc9, Length - Loc9 ) ), MID ( Store[Product Path], 1 + Loc9, Loc10 - Loc9 - 1 ) )
        VAR P11 = IF ( Loc11 = 0, IF ( Loc10 <> 0, MID ( Store[Product Path], 1 + Loc10, Length - Loc10 ) ), MID ( Store[Product Path], 1 + Loc10, Loc11 - Loc10 - 1 ) )
        VAR P12 = IF ( Loc12 = 0, IF ( Loc11 <> 0, MID ( Store[Product Path], 1 + Loc11, Length - Loc11 ) ), MID ( Store[Product Path], 1 + Loc11, Loc12 - Loc11 - 1 ) )
        VAR P13 = IF ( Loc13 = 0, IF ( Loc12 <> 0, MID ( Store[Product Path], 1 + Loc12, Length - Loc12 ) ), MID ( Store[Product Path], 1 + Loc12, Loc13 - Loc12 - 1 ) )
        VAR P14 = IF ( Loc14 = 0, IF ( Loc13 <> 0, MID ( Store[Product Path], 1 + Loc13, Length - Loc13 ) ), MID ( Store[Product Path], 1 + Loc13, Loc14 - Loc13 - 1 ) )
        VAR P15 = IF ( Loc15 = 0, IF ( Loc14 <> 0, MID ( Store[Product Path], 1 + Loc14, Length - Loc14 ) ), MID ( Store[Product Path], 1 + Loc14, Loc15 - Loc14 - 1 ) )
        --VAR P3 = IF ( Loc3 <> 0, MID ( Store[Product Path], 1 + Loc2, Loc3 - Loc2 - 1 ), IF ( Loc2 <> 0, MID ( Store[Product Path], 1 + Loc2, Length - Loc2 ) ) )
        --VAR P4 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc3, Loc4 - Loc3 - 1), IF ( Loc3 <> 0, MID ( Store[Product Path], 1 + Loc3, Length - Loc3 ) ) )
        --VAR P5 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc4, Loc5 - Loc4 - 1), IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc4, Length - Loc4 ) ) )
        --VAR P6 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc5, Loc6 - Loc5 - 1), IF ( Loc5 <> 0, MID ( Store[Product Path], 1 + Loc5, Length - Loc5 ) ) )
       -- VAR P7 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc6, Loc7 - Loc6 - 1), IF ( Loc6 <> 0, MID ( Store[Product Path], 1 + Loc6, Length - Loc6 ) ) )
        --VAR P8 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc7, Loc8 - Loc7 - 1), IF ( Loc7 <> 0, MID ( Store[Product Path], 1 + Loc7, Length - Loc7 ) ) )
        --VAR P9 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc8, Loc9 - Loc8 - 1), IF ( Loc8 <> 0, MID ( Store[Product Path], 1 + Loc8, Length - Loc8 ) ) )
        --VAR P10 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc9, Loc10 - Loc9 - 1), IF ( Loc9 <> 0, MID ( Store[Product Path], 1 + Loc9, Length - Loc9 ) ) )
        --VAR P11 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc10, Loc11 - Loc10 - 1), IF ( Loc10 <> 0, MID ( Store[Product Path], 1 + Loc10, Length - Loc10 ) ) )
        --VAR P12 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc11, Loc12 - Loc11 - 1), IF ( Loc11 <> 0, MID ( Store[Product Path], 1 + Loc11, Length - Loc11 ) ) )
        --VAR P13 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc12, Loc13 - Loc12 - 1), IF ( Loc12 <> 0, MID ( Store[Product Path], 1 + Loc12, Length - Loc12 ) ) )
        --VAR P14 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc13, Loc14 - Loc13 - 1), IF ( Loc13 <> 0, MID ( Store[Product Path], 1 + Loc13, Length - Loc13 ) ) )
        --VAR P15 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc14, Loc15 - Loc14 - 1), IF ( Loc14 <> 0, MID ( Store[Product Path], 1 + Loc13, Length - Loc14 ) ) )
        RETURN
            {  (Loc1, P1), (Loc2, P2), (Loc3, P3), (Loc4, P4), (Loc5, P5), (Loc6, P6), (Loc7, P7), (Loc8, P8), (Loc9, P9), (Loc10, P10), (Loc11, P11), (Loc12, P12), (Loc13, P13), (Loc14, P14), (Loc15, P15) }
    )
VAR Table3 =
    DISTINCT (
        SELECTCOLUMNS ( 
            Table2,
            "Value1", [Value1],
            "Value2", [Value2]
        )
    )
VAR Table4 =
    ADDCOLUMNS (
        Table3, 
        "@Repeated",
        VAR CurrentProduct = [Value2] 
        RETURN
        COUNTROWS (   
            FILTER (
                Table3,
                    [Value2] = CurrentProduct
                        && NOT ISBLANK ( [Value2] )
            )
        )
    )
VAR Table5 =
    SELECTCOLUMNS (
        ADDCOLUMNS ( 
            Table4,
            "@Result", [@Repeated] & [Value2]
        ),
        "Result", [@Result]
    ) 
VAR Result =
    CONCATENATEX ( DISTINCT ( Table5 ), [Result], "," & UNICHAR ( 10 ) )
RETURN 
    Result

12 Replies

johnt75
Super User
4 years ago
I think this would be easier to do in Power Query if you can. I'd create a custom function which uses Text.Split, then you could turn the resulting list into a table, perform a group by aggregation to count the rows for each value, append each value-count pair together then combine the whole lot back into a string which you can return

AntrikshSharma

Community Champion

4 years ago

Anonymous

ChannaCol = 
VAR CurrentPath = Channa[Product Path]
VAR CurrentPathCount =
    LEN ( CurrentPath )
VAR RemoveComma =
    SUBSTITUTE ( CurrentPath, ",", "" )
VAR DelimeterCount =
    1 + CurrentPathCount
        - LEN ( RemoveComma )
VAR CurrentPathLengthSeries =
    SELECTCOLUMNS ( GENERATESERIES ( 1, DelimeterCount, 1 ), "Index", [Value] )
VAR AttachCurrentPathToSeries =
    ADDCOLUMNS (
        CurrentPathLengthSeries,
        "@CurrentAlphabet", MID ( RemoveComma, [Index], 1 )
    )
VAR AlphabetGroups =
    GROUPBY (
        AttachCurrentPathToSeries,
        [@CurrentAlphabet],
        "@Count", COUNTX ( CURRENTGROUP (), [@CurrentAlphabet] )
    )
VAR Result =
    CONCATENATEX ( AlphabetGroups, [@Count] & "-" & [@CurrentAlphabet], ", " )
RETURN
    Result

For a measure just change first line to

VAR CurrentPath = SELECTEDVALUE ( Channa[Product Path] )

Whitewater100
Solution Sage
4 years ago
Hello:
You can try
Result -
var _path = 'Table Name'[Product Path]
return
COUNTROWS(
FILTER(ALL(
'Table Name'),
'Table Name'[Path] = _Path)
)
- Anonymous
  Not applicable
  4 years ago
  Whitewater100 Thanks for the reply. I need count of each value repated in a string along with values. ( Like in expected result column ).
  
  Customer ID | Product Path | Expected Result
  1 A,A,B,B,B,B,B,C,C,C 2-A, 5-B, 3-C
  2 G,H,A,B,B 1-G, 1-H, 1-A, 2-B
  - Whitewater100
    Solution Sage
    4 years ago
    Hi Channa:
    
    I see. I'll attach the file for you, link at the bottom. There is a few things you can do in the Query Editor to get the data in shape. My source file looked like this:
    Cust_ID Prod_Path
    1 A,A,B,B,B,B,B,C,C,C
    2 G,H,A,B,B
    
    https://drive.google.com/file/d/1Zf5PMybceFyUmqKI04ufzavEQnvLkV6b/view?usp=sharing
tamerj1
Community Champion
4 years ago
When you "values" are you referring to a single character string or it could br multiple characters. And if multiple, is it a fixed size value or values can vary in their size (number of characters)?
- Anonymous
  Not applicable
  4 years ago
  tamerj1 I am looking for repeated values. Like if a customer had 3 A and 2 B so I need them in a one string separated by ','.
  - tamerj1
    Community Champion
    4 years ago
    I understand. But the "A" and the "B" are they always single characters? or they can be a string or characters like HI,HI,HELLO,CAR,CAR ... ?

Forum Discussion

Count repeated values in a row

12 Replies

Related content

Count repeating values

Count repeating values

Bottom 10 considering repeated value.

Count if row value repeats. Count number of times value repeats in column

Counting nulls

Cust_ID	Prod_Path
1	A,A,B,B,B,B,B,C,C,C
2	G,H,A,B,B