cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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:

 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:

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.

3 REPLIES 3
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.
Super User

@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

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors