cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
runebruun
Regular Visitor

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
runebruun
Regular Visitor

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

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

"

View solution in original post

4 REPLIES 4
stretcharm Memorable Member
Memorable Member

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

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

 

Community Support
Community Support

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

Hi @runebruun.

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
runebruun
Regular Visitor

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

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

"

View solution in original post

Highlighted
Paulina
Frequent Visitor

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

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors