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

Let say i have this table

Column Name
Hydrogenxxx, Lithiumyyy, Natriumzzz
Hydrogenaaa, Natriumbbb, Natriumccc
Hydrogenaaa
 
Lithium<ddd
Natriumccc
Natriumeee
Lithiumfff
 
 

 

So there are blank rows.

I would like to identify how many times occurence of Natrium in this column.

1st row : 1

2nd row : 2

6th row : 1

7th row : 1

There is "," (comma) delimeter between each string. 

 

Any idea? Thanks

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

9 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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 )
    )
    • Itsbibin's avatar
      Itsbibin
      Regular Visitor
      2 years ago

      Hi tamerj1 

       

      Ihave 2 table in my Power BI report B-Details and SPResult. SP result table is a stored procedure and will change dynamically with parameters. I want to create a new table from this in which it should filter Name and Country from B-Details and SPResult. Now after filtering this we have a uniquekey in B-Details. Now in the new table for each UniqueKey we have to get all the filtered values from SPResult table. So in the new table UniqueKey key row will be duplicated with as much rows we have in SPResult. Can you help me how to fix this?

       

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        2 years ago

        Hi Itsbibin 
        Please provide some sample data along with the expected result.

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    dannytan1112 

     

     

     

    *If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.

  • 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: