Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-yuta-msft
Community Support
Community Support

@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
)

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

Anonymous
Not applicable

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
v-yuta-msft
Community Support
Community Support

@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
)

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.

Anonymous
Not applicable

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
Not applicable

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

 

My hair is going grey 😞

Anonymous
Not applicable

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
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.