New Contributor

# Counting Unique Values with Multiple Criteria

I am using the following formula to count the amount of unique freight carriers I use each day at work:

=SUM(--LEN(UNIQUE(FILTER(Orders[Carrier],Orders[Shipped]=Dates[@[11/22]],"")))>0))

Orders[Carrier] has the name of the freight carrier for each order, Orders[Shipped] has the date the order shipped, and Dates[@[11/22]] is from a table that is just a list of dates to reference in formulas so that certain ones are easier to copy/paste.

But now, I'm trying to modify the same formula to count the amount of unique carriers used each month:

=SUM(--LEN(UNIQUE(FILTER(Orders[Carrier],AND(Orders[Shipped]>=AT3,Orders[Shipped]<=AT32),"")))>0))

AT3 shows 11/1/2022, and AT32 shows 11/30/2022.

But...this formula returns 0. How do I make the formula so that it counts the amount of unique carriers used between 11/1 and 11/30?

3 Replies

# Re: Counting Unique Values with Multiple Criteria

AND returns only a single TRUE/FALSE value, not an array. Try

=SUM(--LEN(UNIQUE(FILTER(Orders[Carrier],(Orders[Shipped]>=AT3)*(Orders[Shipped]<=AT32),"")))>0))

# Re: Counting Unique Values with Multiple Criteria

Since you are using FILTER, you may have LAMBDA.  AND can be made to operate pairwise by placing it with the MAP helper function

``````= UNIQUE(
FILTER(Orders[Carrier],
MAP(Orders[Shipped],
LAMBDA(shippingDate,
AND(shippingDate>=start, shippingDate<=end)
)
)
)
)``````

Naming the Lambda functions can also be useful

``````= UNIQUE(
FILTER(Orders[Carrier],
MAP(Orders[Shipped], Betweenλ(start, end))
)
)``````

where

``````Betweenλ
= LAMBDA(start,end,
LAMBDA(shippingDate,
AND(shippingDate>=start,shippingDate<=end
)
)``````

File