Forum Discussion
Wildcard vlookup
Hello,
I have 2 tables. First table contains applications and correpsonding error messages:
Table 1:
| Application Name | Error message |
| Application 1 | Error occurred: Netork is not available. Please check Network connection. |
| Application 1 | Error: Service is down |
| Application 2 | Error: Service might be down. |
| Application 2 | Error occurred: Upstream service could not be contacted |
| Application 1 | Error occurred: Network service is not available |
| Application 3 | Error 500: Application encountered unknown error |
| Application 3 | Error 403: Forbidden. Access is not granted |
| Application 2 | Error occurred: Service is down |
Second table contains error message pattarens (wildcard) and corresponding categories:
Table 2:
| Pattern | Category |
| Error*Network*not available | Network issue |
| Service*down | Service down |
| Application*error | Application Error |
I need resulting table that would match corresponding category to each error:
Table 3:
| Application Name | Error message | Category |
| Application 1 | Error occurred: Netork is not available. Please check Network connection. | Network issue |
| Application 1 | Error: Service is down | Service down |
| Application 2 | Error: Service might be down. | Service down |
| Application 2 | Error occurred: Upstream service could not be contacted | Unknown |
| Application 1 | Error occurred: Network service is not available | Network issue |
| Application 3 | Error 500: Application encountered unknown error | Application Error |
| Application 3 | Error 403: Forbidden. Access is not granted | Unknown |
| Application 2 | Error occurred: Service is down | Service down |
Tried CONTAINSSTRING and MATCH and few other things but nothing seemed to work. Is it possible at all in DAX?
Would appreaciate any help. Thanks!
4 Replies
- DataInsights
Super User
Try this calculated column in Table 1:
Category = VAR vErrMsg = Table1AppError[Error message] VAR vResult = MAXX ( Table2ErrorCat, VAR vPattern = Table2ErrorCat[Pattern] VAR vCategory = Table2ErrorCat[Category] VAR vStartPos = SEARCH ( vPattern, vErrMsg, 1, 0 ) RETURN IF ( vStartPos <> 0, vCategory ) ) RETURN IF ( ISBLANK ( vResult ), "Unknown", vResult )Hi im trying out this and hit an error at
VAR vErrMsg = Table1AppError[Error message]
"A single value for column 'Column1' in table 'Sheet2' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."- DataInsights
Anonymous,
Measures need an aggregate function (min, max, etc.). My solution is a calculated column, which has a row context (thus, no aggregate function is needed).
