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

[juansgutierrezu]

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

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

[MarcelBeug · Accepted solution]

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.

[juansgutierrezu]

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!