Counting Unique Values with Multiple Criteria

New Contributor

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

@and_rogynous 

 

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

@and_rogynous 

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