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

      • Itsbibin's avatar
        Itsbibin
        Regular Visitor
        2 years ago

        tamerj1 

        This is the dax that I tried but it's not working.

         

        CombinedTable =
        VAR ProductionType = FILTER(RELATEDTABLE('MarketPlan'), 'MarketPlan'[ProductionTypeCode])
        VAR PeriodNO = FILTER(RELATEDTABLE('MarketPlan'), 'MarketPlan'[PeriodNum])
        VAR FilteredBD = FILTER(ALL('SP_Result'),
          'SP_Result'[ProductionTypeCode] = ProductionType &&
          'SP_Result'[PeriodNum] = PeriodNO
        )

        VAR UniqueKeys = SUMMARIZE('MarketPlan', 'MarketPlan'[UniqueKey])

        RETURN
        ADDCOLUMNS(
           FILTER(UniqueKeys, TRUE),
           "MarketPlanData", SUMMARIZE(
                               'MarketPlan',
                               'MarketPlan'[UniqueKey],
                               'MarketPlan',  // Include all columns from 'MarketPlan'
                               'SP_Result'[modeoftransportcode],
                               'SP_Result'[modeoftransportname],
                               'SP_Result'[planningmarketcode],
                               'SP_Result'[planningmarketname],
                               'SP_Result'[InshopWeek],
                               'SP_Result'[percentageVal]
                           )
        )