Issue with DAX "Add custom Column" and nested if statements

[Anonymous]

I have a dataset with 460000 rows (in .xlsx) with a rather complex nested if statement:

= Table.AddColumn(#"Added Custom", "Korr_konto", each if [Konto] = "9200" and [FORMÅL] = "10; Debiterbart" then "9200a"
else if [Konto] = "9200" and [FORMÅL] <> "10; Debiterbart" and [Deb.typ] = "10; Debiterbar" then "9200b"
else if [Konto] = "9200" and [FORMÅL] <> "10; Debiterbart" and [Deb.typ] = "20; Ej debiterbar" then "9200c"
else if [Konto] = "7100" and [FORMÅL] = "10; Debiterbart" then "7100a"
else if [Konto] = "7100" and [FORMÅL] <> "10; Debiterbart" then "7100b"
else if [Konto] = "7110" and [FORMÅL] = "10; Debiterbart" then "7110a"
else if [Konto] = "7110" and [FORMÅL] <> "10; Debiterbart" then "7110b"
else if [Konto] = "7142" and [FORMÅL] = "10; Debiterbart" then "7142a"
else if [Konto] = "7142" and [FORMÅL] <> "10; Debiterbart" then "7142b"
else if [Konto] = "7143" and [FORMÅL] = "10; Debiterbart" then "7143a"
else if [Konto] = "7143" and [FORMÅL] <> "10; Debiterbart" then "7143b"
else if [Konto] = "7160" and [FORMÅL] = "10; Debiterbart" then "7160a"
else if [Konto] = "7160" and [FORMÅL] <> "10; Debiterbart" then "7160b"
else if [Konto] = "7161" and [FORMÅL] = "10; Debiterbart" then "7161a"
else if [Konto] = "7161" and [FORMÅL] <> "10; Debiterbart" then "7161b"
else if [Konto] = "7162" and [FORMÅL] = "10; Debiterbart" then "7162a"
else if [Konto] = "7162" and [FORMÅL] <> "10; Debiterbart" then "7162b"
else [Konto])

This works great when the source is from .xlsx.

But when I imported this into an MS Access DBtable and changed the source from .xlsx to the new DBTable, i got an error message on the "Added Custom" step in the query editor (for the new complex Custom column) - all of the data/columtypes etz. are identical:

"DataFormat.Error: Expression too complex in query expression 'switch(
        ([_].[Konto] = '9200' and [_].[Konto] is not null) and ([_].[FORMÅL] = '10; Debiterbart' and [_].[FORMÅL] is not null), '9200a',
        (([_].[Konto] = '9200' and [_].[Konto] is not null) and ([_].[FORMÅL] <> '10; Debiterbart' or [_].[F'.
Details:
    Hovedboktrans_database.accdb"

When I removed the last 4 nested if statements (so its 13 istead of 17) it works fine....  Anyone has an idea as to why?  Is there a limitation on the number of nested ifs?

[v-shex-msft]

Hi @Anonymous.

You can try to use below custom function to check conditions:

Function:

let
    Check= (Konto as text, formal, deb.typ) =>
    let
        conditional= if deb.typ <> null then "typeC" else if formal <> null then "typeB" else "typeA",
        A= Konto,
        B= if formal= "10; Debiterbart" then "a" else if formal <>"10; Debiterbart" then "b" else "",
        C= if deb.typ ="10; Debiterbart" and formal <>"10; Debiterbart" then "b" else if deb.typ ="20; Debiterbart" and formal <>"10; Debiterbart" then "c" else "",
        result = if conditional = "typeC" then A&C else if conditional = "typeB" then A&B else A
    in
        result
in 
    Check

Use:

Custom=Table.AddColumn(#"Added Custom", "Korr_konto", each Check([Konto],[FORMÅL],[Deb.typ]))

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Thanks,

I got a tip for a less "advanced" solution:

I devided the if statements into two with the last else = "". Put the two parts in each own new temp colum, the made the new [Korr_konto] with following condition:

"

= Table.AddColumn(#"Added Custom2", "Korr_konto", each if [Korr_konto_temp1] = "" and [Korr_konto_temp2] = "" then [Konto]
else if [Korr_konto_temp1] <> "" then [Korr_konto_temp1]
else if [Korr_konto_temp2] <> "" then [Korr_konto_temp2]
else "Error")

"

[Anonymous]

I've recently had this unpleasant surprise with Access source so thanks for this solution. 🙂

[stretcharm]

No I've not seen this error.

Is it possible to make a mapping table

Create a calc for when FORMÅL="10; Debiterbart" and the concatinate the Konto, IsFormal10 and Deb.typ with a delimiter as a Key.

Then left Join to your mapping table which has the same Key

You can then set any missng mappings to Konto