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
kcantor Super Contributor
Super Contributor

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.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




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

Sean Super Contributor
Super Contributor

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.

kcantor Super Contributor
Super Contributor

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?



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Sean Super Contributor
Super Contributor

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

kcantor Super Contributor
Super Contributor

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.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 309 members 3,124 guests
Please welcome our newest community members: