Forum Discussion

dannytan1112's avatar
dannytan1112
Helper I
3 years ago
Solved

String Count with loop in each row

Hi Power BI master around the world,  I have been so active recently because getting interested with this DAX. I have a very difficult measure that i am not sure if it is possible to do in Power B...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi dannytan1112 
    Please refer to attached sample file with the solution

    Count = 
VAR CurrentElelent = SELECTEDVALUE ( Elements[Element] )
RETURN
    SUMX ( 
        VALUES ( 'Table'[Column Name] ),
        VAR String = 'Table'[Column Name]
        VAR Items = SUBSTITUTE ( String, ", ", "|" )
        VAR Length = PATHLENGTH ( Items )
        VAR T1 = GENERATESERIES ( 1, Length, 1 )
        VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
        VAR T3 = FILTER ( T2, CONTAINSSTRING ( [@Item], CurrentElelent ) )
        RETURN
            COUNTROWS ( T3 )
    )
FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi dannytan1112 

you may also create a column like this:

COUNT = (LEN([Column Name]) - LEN(SUBSTITUTE([Column Name], "Natrium", "")))/7

 

tried and it worked like this: