AND
Returns TRUE only when both conditions are true.
AND(<logical1>, <logical2>)Returns: TRUE or FALSE (a scalar).
What it does
AND combines two tests and returns TRUE only when both are true. It is used to build a rule that needs two things at once, such as "over 60 days old and above 1,000 dollars." Inside a FILTER or a CALCULATE, the same logic is more often written with the && operator, which does the same job.
Syntax and arguments
- logical1: the first test, such as
Invoices[DaysPastDue] > 60. - logical2: the second test, such as
Invoices[Amount] > 1000.
AND takes exactly two tests. To combine three, nest AND or use the && operator.
Example
The collections team wants a list of invoices that need a call this week: past due by more than 60 days and above the 1,000 dollar threshold.
Needs Call Flag =
IF(
AND(
Invoices[DaysPastDue] > 60,
Invoices[Amount] > 1000
),
1,
0
)
Each invoice gets a 1 when both conditions hold and a 0 otherwise. The flag can be filtered, summed to count the calls, or used in a visual.
Example: a second, different use
Count the sales lines that were both profitable and returned, which signals a possible quality issue on profitable product:
Profitable Returns =
CALCULATE(
COUNTROWS(Sales),
FILTER(
Sales,
AND(
Sales[SalesAmount] > Sales[TotalCost],
Sales[ReturnAmount] > 0
)
)
)
FILTER passes each row through the AND test, and CALCULATE counts the rows that remain.
Things to watch
- AND is strict. If either test is blank or false, the whole result is not true.
- AND takes two arguments. For more, nest them or use &&.
- In a calculated column, the test runs per row. In a measure, it runs in the filter context of the cell.
- Watch for blanks. A blank is treated as zero in a comparison, so a missing amount can pass a test such as Amount < 1000 without any warning.
- AND returns a logical value, not a number. Wrap it in IF or CALCULATE to produce a count or a flag.
In business terms
AND is used for approval thresholds, eligibility tests and exception reports: a payment that is over a limit and missing a second approver, an account that is both above a balance and past its due date, or a customer that meets both a credit and a payment history rule.