IF Function with 2 Conditions and 2 Type

[sya]

Hi All,

No. of RSO Sent	Ageing - JO IN to RSO (days)
4	NO DATA
4	6
2	1
2	2
2	2
1	3
1	3
1	NO 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

[TheoC]

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]

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 · Accepted solution]

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 🙂

[sya]

It's working! Thanks a lot, Theo. You rock! 🙂

[TheoC]

Nothing at all @sya! A pleasure.

All the best with you BI journey mate!