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.
Hi,
I'm trying to create a custom column with a nested IF and OR function
I need the column to return "Car" if one of the two criteria are met:
If ClaimData[Dx1] IN {"Z11.52", "Z20.822", "Z86.16", "M35.81", "M35.89", "J12.82", "U07.1", "U07.2"
OR IF ClaimData[Dx1] contains {"U10", "U08", "U09", "U12", "U11"}
Can you please assist?
Solved! Go to Solution.
Hi, @AW1976NOVA
Try to create a calculate column below:
Column =
VAR con1 =
IF (
'ClaimData'[Dx1]
IN {
"Z11.52",
"Z20.822",
"Z86.16",
"M35.81",
"M35.89",
"J12.82",
"U07.1",
"U07.2"
},
"TRUE",
"FALSE"
)
VAR con2 =
SWITCH (
TRUE (),
FIND ( "U10", 'ClaimData'[Dx1], 1 ), "TRUE",
FIND ( "U08", 'ClaimData'[Dx1], 1 ), "TRUE",
FIND ( "U09", 'ClaimData'[Dx1], 1 ), "TRUE",
FIND ( "U12", 'ClaimData'[Dx1], 1 ), "TRUE",
FIND ( "U11", 'ClaimData'[Dx1], 1 ), "TRUE",
"FALSE"
)
RETURN
IF ( con1 = "TRUE" || con2 = "TRUE", "Car", "Not Car" )
Note that the FIND function is case-sensitive and the SEARCH function is case-insensitive
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @AW1976NOVA
Try to create a calculate column below:
Column =
VAR con1 =
IF (
'ClaimData'[Dx1]
IN {
"Z11.52",
"Z20.822",
"Z86.16",
"M35.81",
"M35.89",
"J12.82",
"U07.1",
"U07.2"
},
"TRUE",
"FALSE"
)
VAR con2 =
SWITCH (
TRUE (),
FIND ( "U10", 'ClaimData'[Dx1], 1 ), "TRUE",
FIND ( "U08", 'ClaimData'[Dx1], 1 ), "TRUE",
FIND ( "U09", 'ClaimData'[Dx1], 1 ), "TRUE",
FIND ( "U12", 'ClaimData'[Dx1], 1 ), "TRUE",
FIND ( "U11", 'ClaimData'[Dx1], 1 ), "TRUE",
"FALSE"
)
RETURN
IF ( con1 = "TRUE" || con2 = "TRUE", "Car", "Not Car" )
Note that the FIND function is case-sensitive and the SEARCH function is case-insensitive
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @AW1976NOVA ,
there unfortunately no elegant way for the CONTAINS statement.
So I would do it like that:
Car Column =
IF(
ClaimData[Dx1]
IN {
"Z11.52",
"Z20.822",
"Z86.16",
"M35.81",
"M35.89",
"J12.82",
"U07.1",
"U07.2"
}
||
SEARCH("U10", ClaimData[Dx1]) +
SEARCH("U08", ClaimData[Dx1]) +
SEARCH("U09", ClaimData[Dx1]) +
SEARCH("U12", ClaimData[Dx1]) +
SEARCH("U11", ClaimData[Dx1]) > 0 ,
1,
0
)
I'd recommend creating a dimension table that you can build a relationship to or, at the very least, do a LOOKUPVALUE on. If for some reason that's not possible, you can simple do this:
Column =
VAR Variable1 =
IF(
ClaimData[Dx1] = "Z11.52" ||
ClaimData[Dx1] = "Z20.822" ||
.
.
.
ClaimData[Dx1] = "U07.2", 1,0)
VAR Variable2 =
IF(
SEARCH("U10",ClaimData[Dx1],1)>0 ||
SEARCH("U08",ClaimData[Dx1],1)>0 ||
.
.
.
SEARCH("U11",ClaimData[Dx1],1)>0,1,0)
RETURN IF(Variable1 =1 || Variable2=1,"Car","Not a Car")
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |