Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Lobo1908
Helper I
Helper I

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

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:

PNWODateSpare
210297303091189719434575844052021-09-27296170702;296182426;296214872;296182426;296170702;296281097AC;296178922
210287303091189719422487881512021-12-09296170702;296182426;296214872;296281097AC;296174119AB
181087333131064902597248723782022-01-13296208577AB;296178964AE;296208999AB;296208981AB;296172834AD;296285561;296209149;296214830AC;296214335AB
200707303191018113950628929032022-01-26296208577AB;296209032AB;192053363;296109521;296200104AB;296208999AB;296208981AB;296172727AC;296275060AD;296172694AB;296209149
203427303191018118583299929032022-01-26296208577AB;296172694AB;296209032AB;296208999AB;296172727AC;296200104AB;296209149;296275060AD;296178922
181667333091065102784613935492022-01-27296208577AB;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: 

 

PNSpare
1AA:AB
2AA:AC:AD
3AD:
4 
5AA:AD

 

expected result:

 

Spareqty
AA:3
AB1
AC1
AD3

 

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. 

3 REPLIES 3
Lobo1908
Helper I
Helper I

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

@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
Super User
Super User

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.