- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

pls try this
this is better and will work faster
AltTegund =
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"
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks a lot. For some reason I have never used variables, I will do for now.

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
Anonymous
| 06-13-2017 01:23 PM | ||
01-06-2025 01:08 AM | |||
10-20-2024 08:42 AM | |||
03-28-2023 11:50 AM | |||
11-19-2024 03:45 AM |
User | Count |
---|---|
87 | |
81 | |
53 | |
38 | |
35 |