If and or DAX

[Apple08]

Hi

Please could you help me to build up a DAX formula with if, and, or:

If [a] = true
and or [c] = true
then = [d]

I wonder is it possible to sum up the total of and [c] rather than showing the result of [d]?
Many thanks.

 

[Apple08]

Sorry for the typo, the question should be:

If [a] = "1"is true
and [e] ="2" or [c] = "3" is true
then to sum up the d value of [e] and [c]

Also, I would like to create a 'if and' formula with three criteria but it has syntax error, please could someone point out the mistake. Many thanks.


Actual Test = iferror(if(and([project]="A", [Unit]="B",[Name]="C"),[Actual]/[Budget],[Actual])),0)

 

[JustynaMK]

Hi,

AND operator in DAX accepts only two arguments, thus the syntax error. You might want to use "&&" instead:

If([Project] = "A" && [Unit] = "B" && [Name] = "C")

The same goes for OR operator if you ever need to use it - it also accepts only two arguments so you need to replace it with "||".

 

[Apple08]

Thank you JustynaMK. Can I use the && in multiple ifs and I got confused with the right brackets and commas:

If([Project]="A" && [Unit]="B" && [Name] = "C"),[Actual]/100, if([Project]="D" && [Unit] = "E" && [Name] = "F"),[Actual]/200, [Actual])

 

[JustynaMK]

Yes, brackets can usually be a problem I suggest separating your code with "enter" - this one should work:

If(
    [Project]="A" && [Unit]="B" && [Name] = "C",
    [Actual]/100, 
    If(
        [Project]="D" && [Unit] = "E" && [Name] = "F",
        [Actual]/200, 
        [Actual]
    )
)

 

[Apple08]

Brilliant! It works perfectly! Many thanks JustynaMK!

 

[JustynaMK]

You're most welcome!

 

[Apple08]

Please if I want to mix with && and ||, that is Project = A, or Project = B, and Unit = B, Name = C: is the code below correct please?

If(
    [Project]="A" || [Project]="B" && [Unit]="B" && [Name] = "C",
    [Actual]/100, 
    If(
        [Project]="D" && [Unit] = "E" && [Name] = "F",
        [Actual]/200, 
        [Actual]
    )
)​