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

Help with complex nested IF function created as Query, with AND and OR operators

Dear BI community. Once again I ask your kind help with the following issue: I have a table which contains a team description, a date, a test result and a region for every purchaser. I'm trying to create a custom function so that I can invoke  it every time I want on any query. It is a really complex function but in order to sum up, the purchaser that meets the following 3 conditions gets the price:

 

1. The team must be either "A" or the test result must be different to "Negative".

 

2. The date must be between january and june.

 

3. The region must be either "Center" or "East Coast".

 

The table looks as follows: The purchasers that would receive the prize are 1, 3, 8 and 11. Below you can see the table and the code I'm trying to use. I've tried with && and  ‖, but it doesn't work either. I'd really appreciate your help guys!!. Thanks.

 

let
   SuperFunction= (Team,Date,Region,Test) =>

if Team = "A" Or (Test <> "Negative") and (Date>= 1/1/2016 and Date<=6/1/2016) and (Region = "Center" or Region = "East Coast") then "Gets Prize" else

"Doesn't get Prize"

in
    SuperFunction

 

 

 

Table sample.jpgAs I told you before, I'd like to be able to invoke the function, from a query and choose the parameters.

 

 

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

In general, and has a higher priority than or.

The keywords if, then, else, and, or are all lowercase.

For date values, the function #date must be used.

 

Based on these rules, my suggestion would be:

 

let
   SuperFunction= (Team as text, Date as date, Region as text, Test as text) =>

if (Team = "A" or Test <> "Negative") and Date>= #date(2016,1,1) and Date<=#date(2016,6,1) and (Region = "Center" or Region = "East Coast") then "Gets Prize" else

"Doesn't get Prize"

in
    SuperFunction

 

I would expect < #date(2016,6,1) instead of <= #date(2016,6,1), so June 1, 2016 would be excluded.

Specializing in Power Query Formula Language (M)

View solution in original post

2 REPLIES 2
MarcelBeug
Community Champion
Community Champion

In general, and has a higher priority than or.

The keywords if, then, else, and, or are all lowercase.

For date values, the function #date must be used.

 

Based on these rules, my suggestion would be:

 

let
   SuperFunction= (Team as text, Date as date, Region as text, Test as text) =>

if (Team = "A" or Test <> "Negative") and Date>= #date(2016,1,1) and Date<=#date(2016,6,1) and (Region = "Center" or Region = "East Coast") then "Gets Prize" else

"Doesn't get Prize"

in
    SuperFunction

 

I would expect < #date(2016,6,1) instead of <= #date(2016,6,1), so June 1, 2016 would be excluded.

Specializing in Power Query Formula Language (M)

Sir, once again, you saved my life!!! Thanks, I guess I'm used to thinking like Excel and there's a long way to master "M" language. I didn't declare variables and I was forgetting about the lowercase. Anyway, I really find this kind of functions really useful for users like I, who don't have any access to complex data bases and need to create this kind of functions in PQ.

 

Thanks sir!

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.