IF Statement help

[Libbya]

Hi I have created a new column and now trying to create the following If statement to return either 1,2 or 3 in the new column but get a syntax error message. Any suggestions?  

Qtr1 Results = IF('Tier 2 FY23'[Qtr1]='Tier 2 FY23'[Green],"1",If('Tier 2 FY23'[Qtr1]='Tier 2 FY23'[Amber],"2",If('Tier 2 FY23'[Qtr1]='Tier 2 FY23'[Red]="3"))))

Thanks

[Anonymous]

Hi @Libbya ,

I agree with @ryan_mayu . And there is another doubt, whether your [Green], [Amber] and [Red] are text, or a column, if it is text, please use double quotes to quote the text. For example,

Qtr1 Results =
IF (
    'Tier 2 FY23'[Qtr1] = "Green",
    "1",
    IF (
        'Tier 2 FY23'[Qtr1] = "Amber",
        "2",
        IF ( 'Tier 2 FY23'[Qtr1] = "Red", "3" )
    )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@Libbya 

pls try this

Qtr1 Results = IF('Tier 2 FY23'[Qtr1]='Tier 2 FY23'[Green],"1",If('Tier 2 FY23'[Qtr1]='Tier 2 FY23'[Amber],"2",If('Tier 2 FY23'[Qtr1]='Tier 2 FY23'[Red],"3")))

the last equal sign is wrong, should be comma.

[amitchandak]

@Libbya , try like

Qtr1 Results = IF('Tier 2 FY23'[Qtr1]='Tier 2 FY23'[Green],"1",If('Tier 2 FY23'[Qtr1]='Tier 2 FY23'[Amber],"2",If('Tier 2 FY23'[Qtr1]='Tier 2 FY23'[Red]="3")))

Or Try Switch

Qtr1 Results = Switch(True() ,
'Tier 2 FY23'[Qtr1]='Tier 2 FY23'[Green],"1",
'Tier 2 FY23'[Qtr1]='Tier 2 FY23'[Amber],"2",
'Tier 2 FY23'[Qtr1]='Tier 2 FY23'[Red]="3")