Return value for 'text' in multiple columns

[Paudek148]

Hi, 

I have multiple columns with a number of values (in text format) and I want to find the total amount of each value from each columns. See example table below.

These are my data : 

This is the result I want to find : 

I tried this formula : 

Tot mattress= IF(FIND("mattress", 'table'[waste 1 ], 1, 0)<>0,1,0) 

and it works but only for one column. 

Which formula should I use to look each waste in each column?

ex : find the number of time 'mattress' is mentionned in column 'waste 1', 'waste 2' and 'waste 3'.

Thank you 🙂  

[Ritaf1983 · Accepted solution]

Hi @Paudek148 .

In contrast to Excel's engine, which "thinks" cells".
The power bi tool "thinks columns".

As a result, before you start building metrics
In POWER QUERY, the table must be unpivoted .
Please see an attached picture:

you'll get unpivoted table like on picture below:

load it to your model

now you can drag and drop it to the table with counting

Link to sample file 
If my answer was helpful please give me a Kudos and accept as a Solution.