cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sguenther Advocate II
Advocate II

How to avoid nested IF loops

Hello everyone,

 

so I'm in a bit of a pickle. The problem I'm faced with involves filtering Error messages by type out of a database table. So I have a table where each line represents an error that occured. And each error gets a description. But these descriptions aren't identical for each type of error, but have a timestamp or some other random string attached, so I can't just filter them out easily, but have to search for the identifying string and then label the error in a new custom column by type.

 

Right now this looks like this:

 

Error Type =
IF(NOT(ISBLANK(IFERROR( SEARCH( "ExampleErrorString1" , 'webservice jobs'[Error Description] ) , BLANK() ))) , "Error 1" ,
IF(NOT(ISBLANK(IFERROR( SEARCH( "ExampleErrorString2" , 'webservice jobs'[Error Description] ) , BLANK() ))) , "Error 2" ,

.

.

.

"other" ))))))))

 

So in the end I have an IF expression which is nested about 80 times (for the 80 most common errors). Obviously this is not a very efficient code, but I don't know any other way to get what I need. In PowerBi desktop this calculates for a while but gives me the desired result.

 

In the PowerBI dashboard, however, it runs into an error, saying it "exceeds the available resources".

 

Would be great if you could give me a hint how to make this more efficient. I used the nested IF a couple of times in my dashbaords, but the more options you want to check the more inefficient it gets.

 

Thanks,

 

Seb

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft pqian
Microsoft

Re: How to avoid nested IF loops

assuming your timestamp is generated, then they match a specific pattern - one that you can use to split the column into junk and the actual error string.

 

Once you have the cleansed column, you can generate the error type in mutiple ways.

1. In the query editor, use a custom column and the if ... then ... else  M syntax

2. As a calculated column

3. As look up table in the query editor defined as a join.

 

Some other thoughts: if it's possible, you should try to solve this at the source of the problem, when the errors gets generated, insert an error code/type so uniquely identify them. String searchs are never too reliable.

View solution in original post

5 REPLIES 5
Super User I
Super User I

Re: How to avoid nested IF loops

The strings aren't identical, but the pattern must be somewhat predictable. I would approach this by trying to find a way to trim off the excess text rather than finding a match for a set error code. Can you give a couple of examples of what these error strings look like?


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
Super User IV
Super User IV

Re: How to avoid nested IF loops

So, in trying to think this through, the only way I could see this working well is if you could come up with a specific number of characters that would make each specific type of error unique but also ensure that all of the same type of error are the same. You would then grab those using something like LEFT for n characters in a new column. Then, create another table that had all of these unique error type codes (exactly the same as returned by your LEFT n function) in them and perhaps a friendly name. Then, you could relate the two tables based on the LEFT n characters columns and you would have a much more efficient process.

 

If you have some lines that are very long and some lines that are not, such as:

 

Short error message 2015-12-29 10:34 AM

This is a big long error message that does not contain specific error text until right HERE 2015-12-29 10:34 AM

 

The problem is going to be that no single value of "n" will do. So, in that case, create multiple queries that filter out the results by specific lengths, so in query 1, it would only grab values that are 50 characters or less in LEN. You would then have a value for "n" of say 20. You have another query, query 2 that grabs everything that are more than 50 characters in length and this might have an "n" of say 75. Then, you merge the two (or more) queries together so that you end up with table that has all of you error codes trimmed correctly.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Microsoft pqian
Microsoft

Re: How to avoid nested IF loops

assuming your timestamp is generated, then they match a specific pattern - one that you can use to split the column into junk and the actual error string.

 

Once you have the cleansed column, you can generate the error type in mutiple ways.

1. In the query editor, use a custom column and the if ... then ... else  M syntax

2. As a calculated column

3. As look up table in the query editor defined as a join.

 

Some other thoughts: if it's possible, you should try to solve this at the source of the problem, when the errors gets generated, insert an error code/type so uniquely identify them. String searchs are never too reliable.

View solution in original post

sguenther Advocate II
Advocate II

Re: How to avoid nested IF loops

Oh this seems to be probably the best idea. Could you go into some more detail how this would look like?

 

Regarding the error logging: will definitely be changed in the future, but can't be changed for the past. So I have to live with what I have for now and solve it in PowerBI

itchyeyeballs Skilled Sharer
Skilled Sharer

Re: How to avoid nested IF loops

As @KHorseman says, a few examples of the error strings would be useful. If the timestamp is always a set number of characters it should be possible to trim to produce a clean list to work with

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