IF Against Calculate CountRows

[jmfillman]

This function outputs a correct count:

CALCULATE(COUNTROWS (

    FILTER ( 'Stakeholders', CONTAINSSTRING ( 'Stakeholders'[Function], "SME") || CONTAINSSTRING('Stakeholders'[Role], "SME" )))+0)

Anytime I try to put an IF statement around this to check the numeric value of the Calculate function:

IF(CALCULATE(COUNTROWS (

    FILTER ( 'Stakeholders', CONTAINSSTRING ( 'Stakeholders'[Function], "SME") || CONTAINSSTRING('Stakeholders'[Role], "SME" )))+0)>=5,"PASS","FAIL")

The formula doesn't give me an error, but BI Desktop says there is a problem with the calculation, yet gives no clue at to why it cannot assess this formula. At first I thought null values were the issue, but the +0 gives me a value for every row. What am I overlooking here?

[Daniel29195 · Accepted solution]

@jmfillman 

 I would suggest to do the following : 

create 2 columns in the table : 
firs_col =  if( CONTAINSSTRING ( 'Stakeholders'[Function], "SME")  , "YES")

second_col = CONTAINSSTRING('Stakeholders'[Role], "SME" ) , "YES")

then change your measure as follow : 

measure  =

var s = filter ( 'Stakeholders' , 'Stakeholders'[firs_col] = "YES " || 'Stakeholders'[second_col] = "YES " ) 

var res =  countrows ( s ) 

RETURN

switch(

true() , 

res >=5 , "PASS" , "FAIL" ) 

let me know if this helps. 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[jmfillman]

Thank you for the response @Daniel29195. I am quite certain what you proposed will work and will accept it for this post.

What I've found is since the Calculate function returns a number, the true and false result options also have to be numbers. I tested a similar IF statement that returns a string and I have to have a string value for the true and false options.

The below works and I can deal with the numbers instead of the literal string values, although it seems like this shouldn't be necessary and surprised I haven't encountered this before.

IF(CALCULATE(COUNTROWS (

    FILTER ( 'Stakeholders', CONTAINSSTRING ( 'Stakeholders'[Function], "SME") || CONTAINSSTRING('Stakeholders'[Role], "SME" )))+0)>=5, 1, 0)