Forum Discussion
Formatting a terrible code
Hello, I have a calculated column for my dataset that labels each row with a new category based on a set of rules. My code for this is long and ugly and poorly readable IMO. This is the code :
AltTegund = IF(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) in {"4", "6"} && NOT(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) in {"1", "2", "3"}) && NOT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr] in {"47732", "47792", "47520", "47530", "47590", "47702"}), "10 Tekjur",
IF(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 2) = "58" &&NOT(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) in {"1", "2", "3"}), "27 Afskriftir",
IF('Fjárhagsfærslur_Fact'[Tegundalykill_Nr] in {"47732", "47792", "57132", "57812", "57892" && NOT(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) in {"1", "2", "3"}), "57112", "47702", "57192"}, "30 Fjármagnsliðir",
IF('FjárhagsFærslur_Fact'[YfirViðfang_Nr] in {"111"} && NOT(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) in {"1", "2", "3"}) && NOT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr] in {"441502", "46320", "47310", "57132"}), "20 Rekstur fasteigna",
IF((Left('FjárhagsFærslur_Fact'[YfirViðfang_Nr], 1) = "1") && NOT(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) in {"1", "2", "3"}) && NOT('FjárhagsFærslur_Fact'[YfirViðfang_Nr] in {"121"}), "22 Stjórnunarkostnaður",
IF('FjárhagsFærslur_Fact'[YfirViðfang_Nr] in {"121"} && NOT(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) in {"1", "2", "3"}) && NOT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr] in {"47310", "57132"}), "21 Viðhald fasteigna",
IF('Fjárhagsfærslur_Fact'[Tegundalykill_Nr] in {"441502", "46320", "47310", "47802", "441599", "45190", "47320", "47872"} && NOT(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) in {"1", "2", "3"}), "10 Leigutekjur",
IF(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) = "1", "40 Eignir",
IF(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) = "2", "50 Skuldir og eigið fé", "22 Stjórnunarkostnaður"
)))))))))I'm not asking for a formatted version of this abomination but I would appreciate any tips of how to make this cleaner, and if possible, shorter.
Thanks!
Hi 314mp_M0th4 ,
Two things would help a lot here, variables and SWITCH.
For the expressions you're using lots of times, you could use variables instead. Something like,
VAR _firstdigit = LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1)This does two things. You only have to type '_firstdigit' (or whatever name you choose - it can be much shorter) instead of the full LEFT function each time. That calculation is also only done once, then the result is referred to each time.
As you're using lots of IFs you'd be better off by using SWITCH(). Switch takes the first argument, then goes down a list of conditions until it finds a match.
VAR _firstdigit = LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) VAR _number = 'Fjárhagsfærslur_Fact'[Tegundalykill_Nr] RETURN SWITCH( TRUE(), _firstdigit in {"4","6"} && NOT(_firstdigit in {"1","2","3"}) && NOT(_number in {"47732", "47792"}), "10 Tekjur", _firstdigit in ... , "27 Afskriftir", ..., ..., (condition), (result) )Here the first argument is 'TRUE()', so SWITCH checks each condition until one is true. This gives the same results as having lots of IFs all nested together.
If you want to quickly format some code, https://www.daxformatter.com/ is a really good tool.
One last thing, part of your first condition is this:
LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) in {"4", "6"} && NOT(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) in {"1", "2", "3"})The second part here isn't necessary, as any time the first part is true the second is always false (the first digit can't be in both lists at the same time).
Hope that helps.
Antonio
3 Replies
Hi 314mp_M0th4 ,
Two things would help a lot here, variables and SWITCH.
For the expressions you're using lots of times, you could use variables instead. Something like,
VAR _firstdigit = LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1)This does two things. You only have to type '_firstdigit' (or whatever name you choose - it can be much shorter) instead of the full LEFT function each time. That calculation is also only done once, then the result is referred to each time.
As you're using lots of IFs you'd be better off by using SWITCH(). Switch takes the first argument, then goes down a list of conditions until it finds a match.
VAR _firstdigit = LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) VAR _number = 'Fjárhagsfærslur_Fact'[Tegundalykill_Nr] RETURN SWITCH( TRUE(), _firstdigit in {"4","6"} && NOT(_firstdigit in {"1","2","3"}) && NOT(_number in {"47732", "47792"}), "10 Tekjur", _firstdigit in ... , "27 Afskriftir", ..., ..., (condition), (result) )Here the first argument is 'TRUE()', so SWITCH checks each condition until one is true. This gives the same results as having lots of IFs all nested together.
If you want to quickly format some code, https://www.daxformatter.com/ is a really good tool.
One last thing, part of your first condition is this:
LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) in {"4", "6"} && NOT(LEFT('Fjárhagsfærslur_Fact'[Tegundalykill_Nr], 1) in {"1", "2", "3"})The second part here isn't necessary, as any time the first part is true the second is always false (the first digit can't be in both lists at the same time).
Hope that helps.
Antonio
Thanks a lot. For some reason I have never used variables, I will do for now.
pls try this
this is better and will work fasterAltTegund = VAR _Find1 = 'Fjárhagsfærslur_Fact'[Tegundalykill_Nr] VAR _Find2= 'FjárhagsFærslur_Fact'[YfirViðfang_Nr] RETURN SWITCH( TRUE(), LEFT(_Find1, 1) in {"4", "6"} && NOT(LEFT(_Find1, 1) in {"1", "2", "3"}) && NOT(_Find1 in {"47732", "47792", "47520", "47530", "47590", "47702"}), "10 Tekjur", LEFT(_Find1, 2) = "58" &&NOT(LEFT(_Find1, 1) in {"1", "2", "3"}), "27 Afskriftir", _Find1 in {"47732", "47792", "57132", "57812", "57892" && NOT(LEFT(_Find1, 1) in {"1", "2", "3"}), "57112", "47702", "57192"}, "30 Fjármagnsliðir", _Find2 in {"111"} && NOT(LEFT(_Find1, 1) in {"1", "2", "3"}) && NOT(_Find1 in {"441502", "46320", "47310", "57132"}), "20 Rekstur fasteigna", (Left(_Find2, 1) = "1") && NOT(LEFT(_Find1, 1) in {"1", "2", "3"}) && NOT(_Find2 in {"121"}), "22 Stjórnunarkostnaður", _Find2 in {"121"} && NOT(LEFT(_Find1, 1) in {"1", "2", "3"}) && NOT(_Find1 in {"47310", "57132"}), "21 Viðhald fasteigna", _Find1 in {"441502", "46320", "47310", "47802", "441599", "45190", "47320", "47872"} && NOT(LEFT(_Find1, 1) in {"1", "2", "3"}), "10 Leigutekjur", LEFT(_Find1, 1) = "1", "40 Eignir", LEFT(_Find1, 1) = "2", "50 Skuldir og eigið fé", "22 Stjórnunarkostnaður" )
