Forum Discussion
Helper IHelp 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
As I told you before, I'd like to be able to invoke the function, from a query and choose the parameters.
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 SuperFunctionI would expect < #date(2016,6,1) instead of <= #date(2016,6,1), so June 1, 2016 would be excluded.
2 Replies
- MarcelBeug
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 SuperFunctionI would expect < #date(2016,6,1) instead of <= #date(2016,6,1), so June 1, 2016 would be excluded.
juansgutierrezuSir, 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!