Forum Discussion

jmfillman's avatar
jmfillman
Helper I
2 years ago
Solved

IF Against Calculate CountRows

This function outputs a correct count:   CALCULATE(COUNTROWS (     FILTER ( 'Stakeholders', CONTAINSSTRING ( 'Stakeholders'[Function], "SME") || CONTAINSSTRING('Stakeholders'[Role], "SME" )))+0) ...
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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! 🤠

     

Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

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's avatar
jmfillman
Helper I
2 years ago

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)