How to Use NOT IN Query in PowerBI DAX

Question

I have imported Order table from SQL into PowerBI

Order Table has Data Like below.

ID   OrderNo   CustomerNo    OrderDate
1      DC001      1001       2020-06-01
1      DC002      1002       2020-06-09
1      DC003      1003       2020-06-10

Note: I want to Execute below Query in PowerBI DAX

Select Count(Distinct CustomerNo) 
From [order] where orderdate >= '2020-06-08' and orderdate <= '2020-06-14' 
And CustomerNo 
Not in (select CustomerNo from [order] where orderdate < '2020-06-08')

I have tried below code in DAX

MEASURE NOT IN = 
VAR indexList =SELECTCOLUMNS (
    FILTER('Order','Order'[OrderDate] > [RangeFromDate]),"Distict", DISTINCTCOUNT ('Order'[CustomerNo]))
RETURN
    SUMMARIZE (
    FILTER('Order',
        NOT ('Order'[CustomerNo]) IN indexList),
        "Count",Count ( 'Order'[CustomerNo] )
    )

Note: [RangeFromDate] is MEASURE dynamically load From date from the slicer.

But Not Working for me. Kindly Help me to solve this in PowerBI DAX

maximumViable maximumViable · Accepted Answer · 2020-07-10T17:44:00

The EXCEPT function can help with this. It will accept two tables as input, taking the values from the first table, and removing those from the second. I've used a summarize function to ensure each of those tables are distinct (the second table also includes the filter from your SQL subquery). Since they are distinct, I can just COUNTROWS of the resulting table to get the customer count.

RemainingCustomers = 
COUNTROWS(
    EXCEPT(
        SUMMARIZE(Orders, [CustomerNo]), 
        SUMMARIZE(FILTER(Orders, Orders[OrderDate] < DATEVALUE("2020-06-08")) , [CustomerNo])
    )
)

How to Use NOT IN Query in PowerBI DAX

1 Answers