cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Keelin Helper III
Helper III

Switch True or Nested Ifs not working with 3 conditions

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. 

YEARREPORTEDCRSLEVELEXTQUOTA New Calculated Column - Expected Result
2013020 0
2012010 1
2014010 1
2016110 1
2017111 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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support
Community Support

Re: Switch True or Nested Ifs not working with 3 conditions

@Keelin,

 

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
)

Capture.PNG 

 

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.

View solution in original post

Keelin Helper III
Helper III

Re: Switch True or Nested Ifs not working with 3 conditions

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
)

View solution in original post

4 REPLIES 4
Community Support
Community Support

Re: Switch True or Nested Ifs not working with 3 conditions

@Keelin,

 

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
)

Capture.PNG 

 

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.

View solution in original post

Keelin Helper III
Helper III

Re: Switch True or Nested Ifs not working with 3 conditions

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.

Keelin Helper III
Helper III

Re: Switch True or Nested Ifs not working with 3 conditions

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

 

My hair is going grey 😞

Keelin Helper III
Helper III

Re: Switch True or Nested Ifs not working with 3 conditions

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
)

View solution in original post

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

Attending MBAS? Claim your badge

Attending MBAS? Claim your badge

Whether you’re streaming the digital event live, or watching on-demand, claim your attendee badge to sport on your profile.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors