Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adoalan
Helper III
Helper III

Dax Formula help

adoalan_0-1668529281891.png

Hi all,

 

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

 

Can you help

Thank you

 

7 REPLIES 7
adoalan
Helper III
Helper III

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
Community Support
Community Support

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:

vyangliumsft_0-1668578307227.png

 

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

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
Super User
Super User

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

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
Solution Sage
Solution Sage

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!

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],

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.