Trying to create a column to read values in a different column and label the data accordingly.

[Anonymous]

This is what I am trying to write :

Trainer Check = IF(value('RAW (2)'[Trainee Check])="Line Pilot","Non Trainer",IF(value(and(CONTAINS('RAW (2)','RAW (2)'[Trainee Check],"Management")=True,CONTAINS('RAW (2)','RAW (2)'[Trainee Check],"Trainer")=True)),"Trainer",if(value(and(CONTAINS('RAW (2)','RAW (2)'[Trainee Check],"Management")=True,CONTAINS('RAW (2)','RAW (2)'[Trainee Check],"Trainer")=False)),"Management",if(value(CONTAINS('RAW (2)','RAW (2)'[Trainee Check],"Trainer"))="True","Trainer","Non Trainer"))))

And getting this error, "Dax comparison operations do not support comparing values of type number with values of type text. Consider using VALUE or FORMAT fuction to convert one of the value."

I am quite new to Power BI. Looking for assistance.

[v-lionel-msft]

Hi @Anonymous ,

Have your problem been solved?

As tested, @MartynRamsden 's formula is useful, you may need to update your Power BI desktop version.

If you want to create a measure you can do like this:

Measure 2 = 
SWITCH(
    TRUE(),
    MAX('Sales 2015'[Month]) = "January", "Non Trainer",
    OR(
        CONTAINSSTRING( MAX('Sales 2015'[Month]), "February" ) = TRUE,
        CONTAINSSTRING( MAX('Sales 2015'[Month]), "May" ) = TRUE
    ), "Trainer",
    AND(
        CONTAINSSTRING( MAX('Sales 2015'[Month]), "Management" ) = TRUE,
        CONTAINSSTRING( MAX('Sales 2015'[Month]), "Trainer" ) = FALSE
    ), "Management",
    CONTAINSSTRING( MAX('Sales 2015'[Month]), "May" ) = TRUE, "Trainer",
    "Non Trainer"
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MartynRamsden · Accepted solution]

Hi @Anonymous 

First of all, I'm assuming your're wanting to create a calculated column?

When it comes to nested IF statements, I always use the SWITCH function as I find it much easier to read.

You can also make use of the CONTAINSSTRING function which checks for a string within a string.

Try adding the expression below as a calculated column:

Trainer Check =
SWITCH(
    TRUE(),
    'RAW (2)'[Trainee Check] = "Line Pilot", "Non Trainer",
    AND(
        CONTAINSSTRING( 'RAW (2)'[Trainee Check], "Management" ) = TRUE,
        CONTAINSSTRING( 'RAW (2)'[Trainee Check], "Trainer" ) = TRUE
    ), "Trainer",
    AND(
        CONTAINSSTRING( 'RAW (2)'[Trainee Check], "Management" ) = TRUE,
        CONTAINSSTRING( 'RAW (2)'[Trainee Check], "Trainer" ) = FALSE
    ), "Management",
    CONTAINSSTRING( 'RAW (2)'[Trainee Check], "Trainer" ) = TRUE, "Trainer",
    "Non Trainer"
)

Note: CONTAINSSTRING is not case sensitive. If you require case sensitivity, use CONTAINSSTRINGEXACT.

Hope it helps.

Best regards,

Martyn

[Anonymous]

Thanks a ton, however CONTAINSSTRING is failing. I assume because I am not using a SSAS Dbase, but an access database. 

Any Alternatives.

Thanks

Utsav

[MartynRamsden]

Hi @Anonymous 

Is CONTAINSSTRING returning an error or just not returning the results you expect?

If it's the latter, please can you provide some sample data and your expected result?

Best regards,

Martyn

[Anonymous]

Hi Martyn -

Its giving an error. The first one.

[MartynRamsden]

Can you attach an image of the error or, even better, share your PBIX?

[Anonymous]

I am trying but not able to attach the pbix file.

Here's a screenshot of the error.

[MartynRamsden]

Hi @Anonymous 

It looks like you may be running an old version of Power BI desktop.

The CONTAINSSTRING function was made available in the March 2019 release.

Could you install the latest version of Power BI Desktop and try again?

Download Power BI Desktop 

Best regards,

Martyn

[Anonymous]

here you go