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.

bibinthomas's avatar
bibinthomas
Frequent Visitor
2 years ago

Hi tamerj1 

The Table Name PlanDetails:

SupplierCodeSupplierNameSeasonDepatmentCodeDepartmentNameProductionTypeCodeProductTypeNameSectionCodeCountryCodeCountryNameGeographicalCountryIDGroundValueSupplierPlanMonthlyPlanGroundSpreadPlanVersionPlanStatusPlanVersionTypeDataBaseItemPKDataBaseStatusFromSMAPPBackBoneDepartmentIDProductionGroupIDCorportateBrandCodeDivisionPeriodNumUniPeriodKeyPeriodValue%PeriodValueUnikeyBD-SPUniqueKey
1234xyz93030EQ & Local Campaigns63Dresses115IDIndonesia1223010000Sep 2023:0;Oct 2023:0;Nov 2023:5007;Dec 2023:4993;Jan 2024:0;Feb 2024:0;Mar 2024:0;Apr 2024:0;May 2024:0;Jun 2024:0; 3SavedUpdated48304YesNo94510110301156301.12112103101148743030115ID103
12345zzz93090Global Campaigns63Dresses115TRTürkiye1171090100Sep 2023:0;Oct 2023:256.55071628251005;Nov 2023:2352.6083887178706;Dec 2023:4453.429160366789;Jan 2024:10072.229928317362;Feb 2024:14900.493435436674;Mar 2024:17779.051863857374;Apr 2024:24495.031555589398;May 2024:14322.335516082363;Jun 2024:1468.2792359653226; 3SavedUpdated38590YesNo17510110301156301.121009.12103101015413090115TR103
  • 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)