Forum Discussion

sya's avatar
sya
Icon for Helper I rankHelper I
4 years ago
Solved

IF Function with 2 Conditions and 2 Type

Hi All,

 

No. of RSO SentAgeing - JO IN to RSO (days)
4NO DATA
46
21
22
22
13
13
1NO DATA

 

I tried to create a measure that meets the following conditions: 3 RSO Sent Within 3 Working Days

 

So I used the following formula: 

3 RSO Sent in 3 Days = if(VALUE(Tracker[Ageing - JO IN to RSO])<=3 && VALUE(Tracker[No. of RSO Sent])>=3,"Yes","No")
 
But it resulted in the following error:

Please advise on the correct formula. Thanks a lot.

 

Sya

 

Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    Hi sya 

     

    You can create a calculated column to get you the output you're after:

     

    Column = 

    SWITCH ( 
        TRUE () , 
           'Table'[Ageing - JO IN to RSO (days)] = "NO DATA" , BLANK() ,
            VALUE ( 'Table'[No. of RSO Sent] ) >= 3 && VALUE ( 'Table'[Ageing - JO IN to RSO (days)] ) <= 3 , "Yes" , 
            "No" )

    Attached is a PBIX file to assist.


    Theo 🙂

     

5 Replies

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi sya 

     

    Slightly modify your measure to the following:

     

    3 RSO Sent in 3 Days = IFERROR ( IF ( VALUE ( Tracker[Ageing - JO IN to RSO] ) <= 3 && VALUE ( Tracker [No. of RSO Sent] ) >= 3 , "Yes" , "No" ) , 0 )
     
    So all you are doing is wrapping your existing measure with IFERROR ( [existing measure] , 0 ) which says if there's an error, then just return a 0. If you want it to be blank, then just change the 0 to BLANK() like below:
     
    3 RSO Sent in 3 Days = IFERROR ( IF ( VALUE ( Tracker[Ageing - JO IN to RSO] ) <= 3 && VALUE ( Tracker [No. of RSO Sent] ) >= 3 , "Yes" , "No" ) , BLANK() )
     
    Hope this helps!
     
    Theo 🙂
     
    • sya's avatar
      sya
      Icon for Helper I rankHelper I
      4 years ago

      Hi Theo,

       

      I tried the formula that u shared but it results in the following:

       

      And the formula doesn't suggest columns that are already in the table, but only suggested my other created measures to be in the formula.

       

      Sya

      • TheoC's avatar
        TheoC
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Hi sya 

         

        You can create a calculated column to get you the output you're after:

         

        Column = 

        SWITCH ( 
            TRUE () , 
               'Table'[Ageing - JO IN to RSO (days)] = "NO DATA" , BLANK() ,
                VALUE ( 'Table'[No. of RSO Sent] ) >= 3 && VALUE ( 'Table'[Ageing - JO IN to RSO (days)] ) <= 3 , "Yes" , 
                "No" )

        Attached is a PBIX file to assist.


        Theo 🙂