Forum Discussion

juansgutierrezu's avatar
juansgutierrezu
Icon for Helper I rankHelper I
9 years ago
Solved

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 cr...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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.

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

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's avatar
    juansgutierrezu
    Icon for Helper I rankHelper I
    9 years ago

    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!