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)

 

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?

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

     

2 Replies

  • 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)