Sum the occurrences for each value in a column, with multiple values in cells

[Lobo1908]

Hi, 

I'm trying to get the sum of occurrences for each value in a column. 

There are multiple values in most of the cells in that column. 

I need to catch them all, some cells are blank as well. 

Ideally, if there was a measure for it. 

The table looks like this:

PN	WO	Date	Spare
210297303091189719434575	84405	2021-09-27	296170702;296182426;296214872;296182426;296170702;296281097AC;296178922
210287303091189719422487	88151	2021-12-09	296170702;296182426;296214872;296281097AC;296174119AB
181087333131064902597248	72378	2022-01-13	296208577AB;296178964AE;296208999AB;296208981AB;296172834AD;296285561;296209149;296214830AC;296214335AB
200707303191018113950628	92903	2022-01-26	296208577AB;296209032AB;192053363;296109521;296200104AB;296208999AB;296208981AB;296172727AC;296275060AD;296172694AB;296209149
203427303191018118583299	92903	2022-01-26	296208577AB;296172694AB;296209032AB;296208999AB;296172727AC;296200104AB;296209149;296275060AD;296178922
181667333091065102784613	93549	2022-01-27	296208577AB;296261475AB;296209149

No this is rally very simplfied for what the acctual table looks like. 

Ther is much more columns in my model. 

I need to know how many times each value in the "Spare" column occures 

In simple example Im looking for the following:

example  simplyfy Data: 

PN	Spare
1	AA:AB
2	AA:AC:AD
3	AD:
4
5	AA:AD

expected result:

Spare	qty
AA:	3
AB	1
AC	1
AD	3

I'm aware I can pivot the tables and get the solution by puting each spare input in to a separate cell. 

The issue is my data have 500K rows, by pivoting those I will get something between 2-3M rows. 

That will absiclaly kill my report.

I really need to have a mesure to be able to catch this. 

Could someone support please. 

[Lobo1908]

 @amitchandak, I have a measure that uses a lookup table and looks for values from the lookup table in the string. It will work in this case for all the values in the lookup table. The issue is new values are appearing every day and I'm not able to catch those. Updating the lookup table on a daily basis is not an option as well. There must be saolution for this in form of a measure. 

[amitchandak]

@Lobo1908 , I have tried this

Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c

But I doubt this is a good option for large data

[amitchandak]

@Lobo1908 , I know it will increase the data, but  looking at data split by delimiter is the best option

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag