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

[nicktayl]

 

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?

[osei]

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

[Mouchra]

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")

[Mouchra]

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

[mmace1]

It's the | key, typed twice. 

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

[Mouchra]

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

[mmace1]

You can't just replace excel formulas one for one. 

But say here's a wokring example using || - using the built-in sample dataset in power bi

Sales Europe = calculate(sum(financials[ Sales]),financials[Country] = "Canada" || financials[Country] = "Germany")

Or here's a different way using the IN operator - which might be cleaner given how many values you have: 

Sales Europe Dioff way = calculate(sum(financials[ Sales]),financials[Country] IN {"Canada", "France"})

Or - it might be easier in your data, to just add a column to your table that indicates it's in in "Group A", then you can just fliter by the column, rather than have giant dax formulas. 

My advice though would be to write a much shorter measure - make sure you have the syntax right, then expand it. 

[lcdelgado]

Hello! I have the same problem here: Too many arguments were passed to the MAX function. The maximum argument count for the function is 2.

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"))

[OwenAuger]

Hi @lcdelgado 

The immediate issue looks to be a missing right bracket after the first MAX.

I would also suggest using a variable to avoid repeating MAX ( ... ).

This is the code I would suggest:

Measure 2 =
VAR DateDifference =
    MAX ( 'Regulatory Tracker'[Date Difference] )
RETURN
    SWITCH (
        TRUE (),
        DateDifference > 0
            && DateDifference <= 15, "red",
        DateDifference > 15
            && DateDifference <= 30, "yellow",
        DateDifference > 30
            && DateDifference <= 60, "blue",
        DateDifference > 60
            && DateDifference <= 90, "green",
        DateDifference > 90, "green"
    )

[lcdelgado]

Yes it was that! Thanks for reply back to me!! 

[Mouchra]

Thank you mmace1!!

It is working like a charm.

The syntax was incorrect when I put it together.  I will group them in a separate column as you suggested!

[Anonymous]

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

[OwenAuger · Accepted solution]

@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

[lcdelgado]

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]

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

Thanks!

[nicktayl]

@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)

[mmace1]

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]

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...