Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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
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
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
Hi Martyn -
Its giving an error. The first one.
I am trying but not able to attach the pbix file.
Here's a screenshot of the error.
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?
Best regards,
Martyn
here you go
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |