topic Re: Count the number of occurences contained in another columns strings in DAX Commands and Tips

Count the number of occurences contained in another columns strings

CarsteFoRd — Thu, 22 Feb 2024 14:42:06 GMT

Hi all

I am trying to make a new column that count the number of occurences contained in another columns strings

My data looks like this (the desired result is marked in yellow)

Thanks in advance
The Best Carsten

Re: Count the number of occurences contained in another columns strings

Jihwan_Kim — Thu, 22 Feb 2024 17:15:55 GMT

Hi,

Please check the below picture and the attached pbix file.

expected result CC = VAR _maxnumber = MAXX ( ADDCOLUMNS ( Data, "@countdelimiter", LEN ( Data[Path] ) - LEN ( SUBSTITUTE ( Data[Path], "|", "" ) ) ), [@countdelimiter] ) + 1 VAR _generateseriestable = SELECTCOLUMNS ( GENERATESERIES ( 1, _maxnumber, 1 ), "@number", [Value] ) VAR _t = GENERATE ( _generateseriestable, ADDCOLUMNS ( Data, "@item", PATHITEM ( Data[Path], [@number] ) ) ) RETURN COUNTROWS ( FILTER ( _t, [@item] = EARLIER ( Data[ItemID] ) ) )

Re: Count the number of occurences contained in another columns strings

CarsteFoRd — Thu, 22 Feb 2024 19:51:32 GMT

Thanks 🙂