String Count with loop in each row

[dannytan1112]

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 BI.

Let say i have this table

Column Name

Hydrogenxxx, Lithiumyyy, Natriumzzz

Hydrogenaaa, Natriumbbb, Natriumccc

Hydrogenaaa

Lithium<ddd

Natriumccc

Natriumeee

Lithiumfff

So there are blank rows.

I would like to identify how many times occurence of Natrium in this column.

1st row : 1

2nd row : 2

6th row : 1

7th row : 1

There is "," (comma) delimeter between each string. 

Any idea? Thanks

[FreemanZ]

hi @dannytan1112 

you may also create a column like this:

COUNT = (LEN([Column Name]) - LEN(SUBSTITUTE([Column Name], "Natrium", "")))/7

tried and it worked like this:

[tamerj1 · Accepted solution]

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]

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]

Hi @Itsbibin 
Please provide some sample data along with the expected result.

[bibinthomas]

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

[bibinthomas]

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

[bibinthomas]

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)

[Itsbibin]

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

[Mahesh0016]

@dannytan1112 

*If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.