Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
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")
Hi, new at this. What keyboard key is "II"?
It's the | key, typed twice.
The | key is right above the backflash, so you press shift to access it
Thank you, I did that but this is what I got in PBI after replacing OR with ||: The syntaz for '||' is incorrect.
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.
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.
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"
)
Yes it was that! Thanks for reply back to me!!
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!
In stead of " ," YOU CAN USE && AND || . tHIS WILL ALLOW TO HAVE MORE THAN TWO ARGUMENTS.
I have the same problem:
Can you write out an && example? That looks like a better way, but I'm not quite following.
Thanks!
@OwenAuger Beautiful, thanks!
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)
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, ||
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |