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

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:

 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.

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

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors