Help with DAX Formula please

[adoalan]

I have this piece of SQL code and I need to convert it in a DAX formula. 

I got half way through it and the below is working perfectly:

TESTAGAIN =

SWITCH(

    TRUE(),

    ISBLANK('nfp_giftaiddeclaration'[Forename]), "Yes",

    ISBLANK('nfp_giftaiddeclaration'[Surname]), "Yes",

    ISBLANK('nfp_giftaiddeclaration'[House]), "Yes",

    ISBLANK('nfp_giftaiddeclaration'[Postcode]), "Yes",

    "No")

How ever I can't incorporate the last part with 

IF(CONTAINSSTRING( nfp_giftaiddeclaration[Forename] , "&" ) || CONTAINSSTRING(nfp_giftaiddeclaration[Surname], "&" ), "Y", "N")

Somehow I m missing something here and I can't get it done. I get this error:

Can you help?

Many thanks 

[NikhilChenna]

Hi @adoalan ,

Please use the below i took it from your piece of example only,

TESTAGAIN =

SWITCH(

    TRUE(),

    ISBLANK('nfp_giftaiddeclaration'[Forename]), "Yes",

    ISBLANK('nfp_giftaiddeclaration'[Surname]), "Yes",

    ISBLANK('nfp_giftaiddeclaration'[House]), "Yes",

    ISBLANK('nfp_giftaiddeclaration'[Postcode]), "Yes",

    CONTAINSSTRING( nfp_giftaiddeclaration[Forename] , "&" ) || CONTAINSSTRING(nfp_giftaiddeclaration[Surname], "&" ), "Yes", "No")

Check if this solves the issue.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[adoalan]

@NikhilChenna it doesn't work

[NikhilChenna]

Hi @adoalan ,

Try this out then using IF condition 

    IF( 
    ISBLANK('nfp_giftaiddeclaration'[Forename]) || 

    ISBLANK('nfp_giftaiddeclaration'[Surname]) ||

    ISBLANK('nfp_giftaiddeclaration'[House]) ||

    ISBLANK('nfp_giftaiddeclaration'[Postcode]) ||

    CONTAINSSTRING( nfp_giftaiddeclaration[Forename] , "&" ) || 

    CONTAINSSTRING(nfp_giftaiddeclaration[Surname], "&" ), "Yes", 

"No")

OR 

    IF( 
    'nfp_giftaiddeclaration'[Forename]=BLANK() || 

    'nfp_giftaiddeclaration'[Surname]=BLANK() ||

    'nfp_giftaiddeclaration'[House]=BLANK() ||

    'nfp_giftaiddeclaration'[Postcode]=BLANK() ||

    CONTAINSSTRING( nfp_giftaiddeclaration[Forename] , "&" ) || 

    CONTAINSSTRING(nfp_giftaiddeclaration[Surname], "&" ), "Yes", 

"No")

This should work buddy.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[adoalan]

I created coloumn and getting this 

[NikhilChenna]

Hi @adoalan , 

I have replicated the same with a sample table with 3 rows,

Please see you can access the attached one drive link.

IF condition 

I created this table - 

and using this calcution - 

Column =

IF(

'Table'[Forename]=BLANK() ||

'Table'[Surname]=BLANK() ||

'Table'[House]=BLANK() ||

'Table'[Postcode]=BLANK() ||

CONTAINSSTRING('Table'[Forename],"&") ||

CONTAINSSTRING('Table'[Surname],"&"),"Yes",

"No")

You can use this formula directly you just have to replace the table name.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[adoalan]

Still not @NikhilChenna 

[NikhilChenna]

Hi @adoalan ,

I think you are creating a calculated measure.

Create a calculated column.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

@adoalan You can use SEARCH or FIND as an equivalent for CONTAINSSTRING

[adoalan]

@Greg_Deckler I tried but something is not working because I cannot get any results!