Switch True or Nested Ifs not working with 3 conditions

[Anonymous]

Hi All, 

I have a table with four columns I need to use to create a new colum. The forumula is below and the results I expect are in the expected result column. 

Issue is I cannot get teh formula to accept one of the if or switch statements. 

YEAR	REPORTED	CRSLEVEL	EXTQUOTA		New Calculated Column - Expected Result
2013	0	2	0		0
2012	0	1	0		1
2014	0	1	0		1
2016	1	1	0		1
2017	1	1	1		0

Business rules:

All years 2014 and before one treatment (with exception of 2013); and all years after 2015+ another treatment. 

2012 - 2014: REPORTED = 0 & EXTQUOTA = 0; Then new column = 1

2015 - 2018: REPORTED = 1 & EXTQUOTA = 0; Then new column = 1

Exception for CRSLEVEL = 2:

2013: REPORTED = 0 & EXTRAQUOTA = 0 & CRSLEVEL = 2; Then new colum = 0

The formula I am currently trying is:

Reported - Internal 2 = 
SWITCH(
    TRUE(),
    (
        '2012-2018 SES Internal'[YEAR] IN {2012, 2013, 2014} 
        && '2012-2018 SES Internal'[REPORTED] = 0
        && '2012-2018 SES Internal'[EXTQUOTA] = 0),
        1,
    (
        '2012-2018 SES Internal'[YEAR] IN {2015, 2016, 2017, 2018}
        && '2012-2018 SES Internal'[REPORTED] = 1 
        && '2012-2018 SES Internal'[EXTQUOTA] = 0),
         1,
    (
        '2012-2018 SES Internal'[YEAR] IN {2013} 
        && '2012-2018 SES Internal'[EXTQUOTA] = 0
        && '2012-2018 SES Internal'[REPORTED] = 0 
        && '2012-2018 SES Internal'[CRSLEVEL] = 2),
         0
    )

 I was trying to use If statements but I couldnt get those to work either. 

Essentially, it keeps picking up the 2013 records where CRSLevel 2 and EXTQUOTA is 0. There was an error in these records so I dont want to pick them up in the calculation. 

I am really struggling trying to get either a switch true or nested if statemetns to work properly. 

Many thanks

Keelin

[v-yuta-msft · Accepted solution]

@Anonymous,

Modify your calculate column using DAX below:

Reported - Internal 2 = 
SWITCH (
    TRUE (),
    '2012-2018 SES Internal'[YEAR] IN { 2012, 2013, 2014 }
        && '2012-2018 SES Internal'[REPORTED] = 0
        && '2012-2018 SES Internal'[EXTQUOTA] = 0
        && '2012-2018 SES Internal'[CRSLEVEL] <> 2, 1,
    '2012-2018 SES Internal'[YEAR] IN { 2015, 2016, 2017, 2018 }
        && '2012-2018 SES Internal'[REPORTED] = 1
        && '2012-2018 SES Internal'[EXTQUOTA] = 0
        && '2012-2018 SES Internal'[CRSLEVEL] <> 2, 1,
    '2012-2018 SES Internal'[YEAR] IN { 2013 }
        && '2012-2018 SES Internal'[EXTQUOTA] = 0
        && '2012-2018 SES Internal'[REPORTED] = 0
        && '2012-2018 SES Internal'[CRSLEVEL] = 2, 0,
    0
)

 

Community Support Team _ Jimmy Tao

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

[v-yuta-msft · Accepted solution]

@Anonymous,

Modify your calculate column using DAX below:

Reported - Internal 2 = 
SWITCH (
    TRUE (),
    '2012-2018 SES Internal'[YEAR] IN { 2012, 2013, 2014 }
        && '2012-2018 SES Internal'[REPORTED] = 0
        && '2012-2018 SES Internal'[EXTQUOTA] = 0
        && '2012-2018 SES Internal'[CRSLEVEL] <> 2, 1,
    '2012-2018 SES Internal'[YEAR] IN { 2015, 2016, 2017, 2018 }
        && '2012-2018 SES Internal'[REPORTED] = 1
        && '2012-2018 SES Internal'[EXTQUOTA] = 0
        && '2012-2018 SES Internal'[CRSLEVEL] <> 2, 1,
    '2012-2018 SES Internal'[YEAR] IN { 2013 }
        && '2012-2018 SES Internal'[EXTQUOTA] = 0
        && '2012-2018 SES Internal'[REPORTED] = 0
        && '2012-2018 SES Internal'[CRSLEVEL] = 2, 0,
    0
)

 

Community Support Team _ Jimmy Tao

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

[Anonymous]

Thanks so much @v-yuta-msft, although I also need to be able to report a 1 where CRS Level is = 2 as well, just not for 2013! This is why I was trying to not capture it except for in 2013 where the records are not right.

[Anonymous]

Maybe I should add a few more statements to include CRS = 2?

My hair is going grey 😞

[Anonymous · Accepted solution]

Thanks so much @v-yuta-msft

I just added another statement for 2013 as per your example and it works!!! I appreciate this so very much. 

Reported - Internal 2 = 
SWITCH (
    TRUE (),
    '2012-2018 SES Internal'[YEAR] IN { 2012, 2014 }
        && '2012-2018 SES Internal'[REPORTED] = 0
        && '2012-2018 SES Internal'[EXTQUOTA] = 0
        && '2012-2018 SES Internal'[CRSLEVEL]IN{1,2}, 1,
    '2012-2018 SES Internal'[YEAR] IN { 2015, 2016, 2017, 2018 }
        && '2012-2018 SES Internal'[REPORTED] = 1
        && '2012-2018 SES Internal'[EXTQUOTA] = 0
        && '2012-2018 SES Internal'[CRSLEVEL]IN{1,2}, 1,
    '2012-2018 SES Internal'[YEAR] IN { 2013 }
        && '2012-2018 SES Internal'[EXTQUOTA] = 0
        && '2012-2018 SES Internal'[REPORTED] = 0
        && '2012-2018 SES Internal'[CRSLEVEL] = 2, 0,
    '2012-2018 SES Internal'[YEAR] IN { 2013 }
        && '2012-2018 SES Internal'[EXTQUOTA] = 0
        && '2012-2018 SES Internal'[REPORTED] = 0
        && '2012-2018 SES Internal'[CRSLEVEL] = 1, 1,
    0
)