Count Columns In A Record That Contain Specific Text

[Anonymous]

This is probably some basic stuff but I just can't seem to find a solved solution for this...

What would be the most efficient and maintanable method for counting all the columns in a record that contain a specific string?

The use case is an inspection form from SmartSheet with a column for each inspection item. There can be around 30 columns that have the text PASS, FAIL, or N/A.

I'd like to have a column that counts all the occurances of PASS and can adapt to the addition or removal of columns as inspection needs change.

I tried a calculate(countrows...) formula but when I added another column to count the fails I got a circular refrence error.

[kcantor]

Can you share a sample of your data?

My first response is to use the COUNTROWS for the table and wrap it in calculate to filter. Something like:

CALCULATE(

     COUNTROWS('Fact'),

     FILTER('Fact',  [Pass/Fail] ="Pass")

)

You would need to do separate ones for each of the three criteria with that method but without knowing more about your data, that is the best I can come up with off the cuff.

[Sean]

@kcantor I agreewith you @Anonymous needs to provide a sample of what the data looks like

I keep reading his question though and the way he keeps saying Count COLUMNS

I think he want to check and count across ALL Columns on each ROW -  Not Count within a single COLUMN

In either case we need a sample of what the data looks like

EDIT: I guess I was replying while he was sending

[Anonymous]

@Sean Correct. I want a column count where the string is specific. I care nothing for the row count.   And preferably irregardless of the column name.

[Anonymous]

A simplified version of the data looks like this... So the PASS Count for Row 1 should be 1, Row 5 would be 2, and Row 8 would be 0.

[kcantor]

Honestly, if the rows all have seperate Id numbers, I would sum the "pass' scenario then add across for the total. I say that because I would, later, want to calculate the ratios as they pertain to each individual inspection. You would need to use the row ids in the table however but could only display the final measure, not all of the intermediary ones.

Pass Inspection1 = CALCULATE(SUM(COUNTROWS), [Inspection1]="pass")  Same for other columns. then add the measures for a third measure.

Number of Pass = [Pass Inspection 1]+[Pass Inspection2]+[Pass Inspection3]

that would leave room to use each individual measuer to be used in other ways.

Is there a reason for wanting to calculate only for three complete inspections?

[Anonymous]

There's actually about 30+ inspections being logged for the day across 12 nationwide locations by 24 different people. I had been trying your method for awhile today but I kept getting circular reference errors. But for some reason it's all working now. I was trying to avoid this method if I could because of the amount of helper columns needed and the fact that the inspections can and will as needs change.

The report view will display various metrics on common inspection failures, failures associated with locations, equipment, etc.

But if there's not a method to just count across the row then I guess it is what it is.

This was a very simple snippet of the data. I really just needed to figure out how to get the count number so I could do, % scores, averages, etc. And now that the brute force method seems to be working now I think I can get things rolling again. Sorry if I over simplified before.

And.... it looks like I type too slow. 🙂

[Sean]

@Anonymous what @kcantor is suggesting will actually work

Use...

Pass 1 = CALCULATE(COUNTROWS(Table1), Table1[Inspection1]="PASS")

and so on... for Pass 2, etc... Don't know why @kcantor added a SUM

Also for the Total Number

Use...

Num of PASS = IF (ISBLANK ([Pass 1] + [Pass 2] + [Pass 3]), 0, [Pass 1] + [Pass 2] + [Pass 3])

Because if you have NO PASSes on a single row that ROW ID will not show up in a visualization it will not show you 0 it will be hidden

The other thing is you would have to create new Measures Pass 4 etc... as you add Columns

If you need Num of Fail and Num of N/A you just have to create in the same way

I'm guessing there is a much easier way to do this in M???

[kcantor]

@Sean

You are not alone. I have no idea why I added SUM except for maybe were  I was working up a new dataset,my mind was stuck in "SUM" land and muscle memory ticked on when typing a measure. Most new reports start with doing some basic SUMS here.

Good catch on the zero.  Multitasking on a Wednesday is very difficult.

I would prefer to create those different measures because, inevitably, whenever I create a report someone always asks for more. With individual measures it is easier to reiterate into something new.