Dax Formula help

[adoalan]

Hi all,

I have the above formula and I m not sure what Im doing wrong here.

Can you help

Thank you

[adoalan]

I got working this part with if 3 columns are blank 

Missing = IF(ISBLANK(nfp_giftaiddeclaration[Surname]) && ISBLANK(nfp_giftaiddeclaration[Forename]) &&  ISBLANK(nfp_giftaiddeclaration[House]), "Y", "N")

I need this part which says if nfp_declaredfore contains like '%&%' or nfp_declaredsur like '%&%'  then  "Y", "N")

[v-yangliu-msft]

Hi  @adoalan ,

Here are the steps you can follow：

1. Create calculated column.

Missing INFO =
IF(
CONTAINSSTRING([Forename],"&") =TRUE() &&CONTAINSSTRING([Surname],"&")&&'nfp_giftaiddeclaration'[Hourse]=BLANK()
,"Y","N")

2. Result:

Best Regards,

Liu Yang

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

[adoalan]

Hi Liu,

Many thanks for that.

Somehow is not working to me 

I m trying to match this sql formula so some ae with blank and some with if contain. 

CASE WHEN gad.nfp_declaredfore IS NULL OR gad.nfp_declaredfore = '' OR gad.nfp_declaredsur IS NULL OR gad.nfp_declaredsur = ''
OR gad.nfp_declaredhouse IS NULL OR gad.nfp_declaredhouse = '' or nfp_declaredpostcode is null or nfp_declaredpostcode = ''
or nfp_declaredfore like '%&%' or nfp_declaredsur like '%&%' then 'Y' ELSE 'N' END AS [Missing Info],

[Syk]

It looks like you're trying to wrap up that if statement but you're missing a ) on the last CONTAINSSTRING function.

[adoalan]

HI Syk,

I added and still not working 

Many thanks for that.

I m trying to match this sql formula so some ae with blank and some with if contain. 

CASE WHEN gad.nfp_declaredfore IS NULL OR gad.nfp_declaredfore = '' OR gad.nfp_declaredsur IS NULL OR gad.nfp_declaredsur = ''
OR gad.nfp_declaredhouse IS NULL OR gad.nfp_declaredhouse = '' or nfp_declaredpostcode is null or nfp_declaredpostcode = ''
or nfp_declaredfore like '%&%' or nfp_declaredsur like '%&%' then 'Y' ELSE 'N' END AS [Missing Info],

[PabloDeheza]

Hi @adoalan !
You are not using CONTAINSSTRING correctly, as the error message says, you are providing the function more arguments than it can take. 
The funcition only works with two arguments, the first one is where it checks, and the second one is the text to look for.
Both of your CONTAINSSTRING should be closed right after "&", if you one to check for more than one string you can try with OR conditions like this:
CONTAINSSTRING( Column1, "&" ) && ( CONTAINSSTRING( Column2, "&" ) || CONTAINSSTRING( Column2, "Y" ) || CONTAINSSTRING( Column2, "N" ) )

Let me know if that helps!

[adoalan]

Hi Pablo,

Many thanks for that.

I m trying to match this sql formula so some are with blank and some with if contain. 

CASE WHEN gad.nfp_declaredfore IS NULL OR gad.nfp_declaredfore = '' OR gad.nfp_declaredsur IS NULL OR gad.nfp_declaredsur = ''
OR gad.nfp_declaredhouse IS NULL OR gad.nfp_declaredhouse = '' or nfp_declaredpostcode is null or nfp_declaredpostcode = ''
or nfp_declaredfore like '%&%' or nfp_declaredsur like '%&%' then 'Y' ELSE 'N' END AS [Missing Info],