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 )
    )
bibinthomas's avatar
bibinthomas
Frequent Visitor
2 years ago

Hi tamerj1 

The 2nd Table is StoredProcedureresult that changes when parameters are changed

seasonMOTcodeMOTnamecountryofproductionplanningmarketcodeproductionGroupIdProductionTypeCodeInshopWeekPeriodNumDivisionpercentageValLLTInshopWeek - CopyISW-YearISW-WeekTODTODMonth
71SeaIN1026106320235001030.56262023502023502023442023-10
71SeaIN1011106320235001030.44682023502023502023422023-10
71SeaIN1006106320235001030.41472023502023502023432023-10
71SeaID1011106320235101031.29692023512023512023422023-10
71SeaMM1026106320234901036.72672023492023492023422023-10
bibinthomas's avatar
bibinthomas
Frequent Visitor
2 years ago

Hi tamerj1  I have sent the Two table data, Here the tables are filtered based on ProductionTypecode and PeriodNum from both tables, after filtering each row from PlanDetails has a UniqueKey row, This should result in a table where each UniqueKey row from "PlanDetails" is duplicated with all the data from "Bought Details" and combined with the filtered data from "StoredprocedureResult" for each row based on the specified conditions(from Stored Procedure, the columns we need from SP result are MOTcode MOTname planningmarketcode InshopWeek percentageVal LLT InshopWeek - Copy ISW-Year ISW-Week TOD TODMonth)