Forum Discussion
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?
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???
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.