Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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 )
@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.
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.
Maybe I should add a few more statements to include CRS = 2?
My hair is going grey 😞
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |