How to count a text value in multiple columns for each row

[PMorfitt]

Hi All,

i have a set of data which has the following:

Products with unique identifier. Each product should have a set of documents. The data set i have has gaps with which documents are avalible for the product. I would like to visually show number of missing documents as well as which documents are avaliable. 

Product Code	Product Name	Doc Type
1234	Apple	Paper
1234	Apple	Ink
1234	Apple	Pen
4321	Banana	Paper
4321	Banana	Pen
2345	Pear	Shrink Wrap

So what i did was created a list from column 'doc type', so the unique count of the product and created custom columns using the following formula: Added contitional column, , each if text.contains ( ) then true else false) ,to give a true/false indication if a certain document is there or not. 

Product Code	Product Name	Doc Type.List	Conditional column.Paper	Conditional column.Shrink Wrap	Conditional column. Pen
1234	Apple	Paper, Shrink Wrap, Pen	True	True	True
4321	Banana	Pen, Paper	True	False	True
2345	Pear	Shrink Wrap	False	True	False

What i would like to do is count the number of 'False' across the conditional column for each row. 

Can someone help?

Thank you in advance. 

[Anonymous · Accepted solution]

Hi @PMorfitt ,

Please create a calculated column as below.

Column = IF('Table'[Conditional column.Paper]="false",1,0)+IF('Table'[Conditional column. Pen]="false",1,0)+IF('Table'[Conditional column.Shrink Wrap]="false",1,0)

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VasTg]

@PMorfitt 

You could add a custom column to get the count of False as below

=Number.Abs((Number.From([Conditional column.Paper])-1)+(Number.From([Conditional column.Shrink Wrap])-1)+(Number.From([Conditional column. Pen])-1))

If it helps, mark it as a solution

Kudos are nice too

[PMorfitt]

@VasTgThank you for sharing, i've tried as you've suggested however i recieved an error message:

Expression.Error: We cannot apply operator - to types Logical and Number.
Details:
Operator=-
Left=FALSE
Right=1

do you mind explaining what this means and how i can adapt the formula you've provided? 

Thanks