How to avoid nested IF loops

[sguenther]

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

[pqian · Accepted solution]

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.

[sguenther]

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

[Greg_Deckler]

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.

[KHorseman]

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?

[itchyeyeballs]

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