I have a table where i need to count the rows of one column by a certain text string but then subtract the count of another column in that same table
To put it into context. I have a injured person table and i need to count the rows where the injury category = " Lost Time" but i need to deduct the number of injured people where they are a non employee, there is a column in the same table labeled ‘Contractor’ but it is a free text field so i need to identify where there is text in this column and then count the rows to deduct from the total rows counted where Injury Category = Lost Time.
I have used Countrows to calculate the number of Lost Time injuries but i cannot work out how to factor in deducting a count of the Contractor column if there is text in that column, I think i need to add the IF function but im not sure how?
This is what i have so far
LTI = Countrows( Filter(‘injured workers’ ,‘injured workers’[injury classification] = “Lost Time”))
Thanks for your help in advance