cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dennerj Frequent Visitor
Frequent Visitor

Count Columns In A Record That Contain Specific Text

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.

8 REPLIES 8
Highlighted
Super User
Super User

Re: Count Columns In A Record That Contain Specific Text

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.

dennerj Frequent Visitor
Frequent Visitor

Re: Count Columns In A Record That Contain Specific Text

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.

 

 

2016-03-30 1-33-08 PM.jpg

Super User
Super User

Re: Count Columns In A Record That Contain Specific Text

@kcantor I agreewith you @dennerj 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

dennerj Frequent Visitor
Frequent Visitor

Re: Count Columns In A Record That Contain Specific Text

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

Super User
Super User

Re: Count Columns In A Record That Contain Specific Text

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?

Super User
Super User

Re: Count Columns In A Record That Contain Specific Text

@dennerj 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

 

 

Count Columns.png

 

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???

Super User
Super User

Re: Count Columns In A Record That Contain Specific Text

@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.

dennerj Frequent Visitor
Frequent Visitor

Re: Count Columns In A Record That Contain Specific Text

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. Smiley Happy