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

    • 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