Wildcard vlookup

[itiutiunnik]

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!

[DataInsights]

@itiutiunnik,

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 )

[Anonymous]

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

[Anonymous]

okay thank you @DataInsights 🙂