Forum Discussion
String Count with loop in each row
Hi dannytan1112
Please refer to attached sample file with the solutionCount = 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 ) )
Hi tamerj1
The Table Name PlanDetails:
| SupplierCode | SupplierName | Season | DepatmentCode | DepartmentName | ProductionTypeCode | ProductTypeName | SectionCode | CountryCode | CountryName | GeographicalCountryID | GroundValue | SupplierPlan | MonthlyPlan | GroundSpread | PlanVersion | PlanStatus | PlanVersionType | DataBaseItemPK | DataBaseStatus | FromSMAPPBackBone | DepartmentID | ProductionGroupID | CorportateBrandCode | Division | PeriodNum | UniPeriodKey | PeriodValue% | PeriodValue | UnikeyBD-SP | UniqueKey |
| 1234 | xyz | 9 | 3030 | EQ & Local Campaigns | 63 | Dresses | 115 | ID | Indonesia | 1223 | 0 | 10000 | Sep 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; | 3 | Saved | Updated | 48304 | Yes | No | 945 | 10 | 1 | 103 | 0 | 115630 | 1.12 | 112 | 10310 | 1148743030115ID103 | |
| 12345 | zzz | 9 | 3090 | Global Campaigns | 63 | Dresses | 115 | TR | Türkiye | 1171 | 0 | 90100 | Sep 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; | 3 | Saved | Updated | 38590 | Yes | No | 175 | 10 | 1 | 103 | 0 | 115630 | 1.12 | 1009.12 | 10310 | 1015413090115TR103 |
Hi tamerj1
The 2nd Table is StoredProcedureresult that changes when parameters are changed
| season | MOTcode | MOTname | countryofproduction | planningmarketcode | productionGroupId | ProductionTypeCode | InshopWeek | PeriodNum | Division | percentageVal | LLT | InshopWeek - Copy | ISW-Year | ISW-Week | TOD | TODMonth |
| 7 | 1 | Sea | IN | 1026 | 10 | 63 | 202350 | 0 | 103 | 0.562 | 6 | 202350 | 2023 | 50 | 202344 | 2023-10 |
| 7 | 1 | Sea | IN | 1011 | 10 | 63 | 202350 | 0 | 103 | 0.446 | 8 | 202350 | 2023 | 50 | 202342 | 2023-10 |
| 7 | 1 | Sea | IN | 1006 | 10 | 63 | 202350 | 0 | 103 | 0.414 | 7 | 202350 | 2023 | 50 | 202343 | 2023-10 |
| 7 | 1 | Sea | ID | 1011 | 10 | 63 | 202351 | 0 | 103 | 1.296 | 9 | 202351 | 2023 | 51 | 202342 | 2023-10 |
| 7 | 1 | Sea | MM | 1026 | 10 | 63 | 202349 | 0 | 103 | 6.726 | 7 | 202349 | 2023 | 49 | 202342 | 2023-10 |
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)