Forum Discussion

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

Too many arguments were passed to the AND function. The maximum argument count for the function is 2

 

 

In Access and Excel you can write "AND" or "OR" functions with unlimited conditions. Is this not the case in Power BI DAX "AND" functions? If this is a limitation in DAX, does anyone have a clean way to write the Excel/Access function in Power BI?

Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    nicktayl

    In Power BI, the DAX functions AND and OR each take exactly two arguments.

     

    To handle more than two arguments, use && for "and" and || for "or" (or nest as in earlier post).

    e.g. A && B && C

17 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    9 years ago

    nicktayl

    In Power BI, the DAX functions AND and OR each take exactly two arguments.

     

    To handle more than two arguments, use && for "and" and || for "or" (or nest as in earlier post).

    e.g. A && B && C

    • mmace1's avatar
      mmace1
      Icon for Impactful Individual rankImpactful Individual
      9 years ago

      Can you write out an && example?  That looks like a better way, but I'm not quite following.  

       

      Thanks!

      • nicktayl's avatar
        nicktayl
        Icon for Helper I rankHelper I
        9 years ago

        OwenAuger Beautiful, thanks!

         

        mmace1 

        DAX:

        Purchase Only Flag = If(tbl_kpi[Program]="Purchase" && tbl_kpi[Purchase Price]>0 && (tbl_kpi[Rehab Budget]=0 || isblank(tbl_kpi[Rehab Budget])),"Purchase Only",blank())

         

        Access:

        Purchase Only Flag: IIf([kpi].[Program]="Purchase" And [kpi].[Purchase Price]>0 And ([kpi].[Rehab Budget]=0 Or [kpi].[Rehab Budget] Is Null),"Purchase Only",Null)

    • lcdelgado's avatar
      lcdelgado
      Icon for Helper I rankHelper I
      3 years ago

      I have the same problem:

      Measure 2 = SWITCH(TRUE(),
      MAX('Regulatory Tracker'[Date Difference]>0 && MAX('Regulatory Tracker'[Date Difference])<=15,"red",
      MAX('Regulatory Tracker'[Date Difference])>15 && MAX('Regulatory Tracker'[Date Difference])<=30,"yellow",
      MAX('Regulatory Tracker'[Date Difference])>30 && MAX('Regulatory Tracker'[Date Difference])<=60,"blue",
      MAX('Regulatory Tracker'[Date Difference])>60 && MAX('Regulatory Tracker'[Date Difference])<=90,"green",
      MAX('Regulatory Tracker'[Date Difference])>90,"green"))
      I have this error: Too many arguments were passed to the MAX function. The maximum argument count for the function is 2. ðŸ™„
  • mmace1's avatar
    mmace1
    Icon for Impactful Individual rankImpactful Individual
    9 years ago

    Hi, 

     

    You can actually have unlimited AND/OR in DAX, you just have to write it a little differently. 

     

    Specifically, you have to nest all the AND/ORs above 2.  It's kinda stupid, but it works. 

     

    So say in Excel you'd write: 

     

    =IF(AND(A1=1,B1=2,C1=3),"OK", "oh no")

     

    In DAX you'd write

     

    =IF(AND(A1=1,AND(B1=2,C1=3)),"OK","oh no")

     

    Edit:  Or actually use &&, that's far better.  Or the equivalent for OR, ||

     

    • mmace1's avatar
      mmace1
      Icon for Impactful Individual rankImpactful Individual
      9 years ago

      Well, probalby not unlimited, but I imagine as many as Excel can support.

       

      Anyway, so you can have many AND/OR statements, you just have to keep nesting as you go...

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    In stead of " ," YOU CAN USE && AND || . tHIS WILL ALLOW TO HAVE MORE THAN TWO ARGUMENTS.

     

     

  • Roger_M's avatar
    Roger_M
    Icon for Advocate I rankAdvocate I
    4 years ago

    Hi, new at this.  What keyboard key is "II"?

    • mmace1's avatar
      mmace1
      Icon for Impactful Individual rankImpactful Individual
      4 years ago

      It's the | key, typed twice. 

      The | key is right above the backflash, so you press shift to access it

      • Roger_M's avatar
        Roger_M
        Icon for Advocate I rankAdvocate I
        4 years ago

        Thank you, I did that but this is what I got in PBI after replacing OR with ||: The syntaz for '||' is incorrect.

  • Roger_M's avatar
    Roger_M
    Icon for Advocate I rankAdvocate I
    4 years ago

    This is what I have from excel and need to input to PBI: IF(OR(MI_ENTITIES[LAST_UPBY_SEUS_KEY]="apmc",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="hmmd",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="crry",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="apdc",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="equz",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="gech",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="hqrn",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="phfw",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="ldia",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="ahwt",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="dzmt",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="axbg",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="eovb",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="dwav",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="rfiw",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="kqxu",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="jtvu",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="edwy",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="kqxu",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="hzfc",MI_ENTITIES[LAST_UPBY_SEUS_KEY]="uebq"),"Pass","Fail")

  • osei's avatar
    osei
    New Member
    3 years ago

    After adding the 3rd "if" condition (the bolded text in the formula below), excel is telling me that "you have entered too many arguments for this function", please what should I do

    =IF(AND(J46>0,N41>0),DEGREES(ATAN(2*J46/N41)),IF(AND(J46>0,N41<0),(DEGREES(ATAN(2*J46/N41))+180)),IF(AND(J46<0,N41>0),360+DEGREES(ATAN(2*J46/N41))))/2