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