topic Re: Count repeated values in a row in DAX Commands and Tips

Count repeated values in a row

Anonymous — Thu, 24 Feb 2022 14:23:35 GMT

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.

Re: Count repeated values in a row

Whitewater100 — Thu, 24 Feb 2022 14:49:23 GMT

Hello:

You can try

Result -

var _path = 'Table Name'[Product Path]
return

COUNTROWS(
FILTER(ALL(
'Table Name'),
'Table Name'[Path] = _Path)
)

Re: Count repeated values in a row

Anonymous — Thu, 24 Feb 2022 15:52:09 GMT

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

Re: Count repeated values in a row

johnt75 — Thu, 24 Feb 2022 16:02:40 GMT

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

Re: Count repeated values in a row

Whitewater100 — Thu, 24 Feb 2022 18:16:29 GMT

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

Re: Count repeated values in a row

tamerj1 — Fri, 25 Feb 2022 02:13:52 GMT

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

Re: Count repeated values in a row

Anonymous — Fri, 25 Feb 2022 02:22:49 GMT

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 ','.

Re: Count repeated values in a row

tamerj1 — Fri, 25 Feb 2022 02:28:41 GMT

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

Re: Count repeated values in a row

Anonymous — Fri, 25 Feb 2022 02:31:46 GMT

tamerj1 yes their are several product names in data. like webinars , website, tools...

Re: Count repeated values in a row

tamerj1 — Fri, 25 Feb 2022 03:36:45 GMT

Even if possible in DAX it would be extremely complicated. The only option is to split into columns. I know this will perhaps double the size of your table but I don't see any other way around.

Re: Count repeated values in a row

tamerj1 — Fri, 25 Feb 2022 04:27:41 GMT

Anonymous

What is the maximum number of characters in the whole string?

Re: Count repeated values in a row

tamerj1 — Mon, 28 Feb 2022 11:20:07 GMT

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

Re: Count repeated values in a row

AntrikshSharma — Mon, 28 Feb 2022 18:04:14 GMT

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] )