How to Use NOT IN Query in PowerBI DAX

[Anonymous]

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", DISTINCT ('Order'[CustomerNo]))
RETURN
    SUMMARIZE (
    FILTER('Order',
        NOT ('Order'[CustomerNo]) IN indexList),
        "Count",DISTINCTCOUNT( '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

[Anonymous]

I got the error below error when I pass more lists of values into NOT IN Filter.

Error Message:
MdxScript(Model) (13, 83) Calculation error in measure 'Order'[MEASURE NOT IN]: A table of multiple values was supplied where a single value was expected.

[Mariusz]

Hi @Anonymous 

Can you share your DAX expression?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

MEASURE NOT IN =

VAR indexList =

           SELECTCOLUMNS

           ( FILTER('CompareOrder','CompareOrder'[OrderDate] > [RangeFromDate]),

          "Distict", DISTINCT ('CompareOrder'[CustomerNo]))

RETURN SUMMARIZE (

           FILTER('Order', NOT ('Order'[CustomerNo] IN indexList)),

           "Count",DISTINCTCOUNT ( 'Order'[CustomerNo] ) )

Note:

1. RangeFromDate have data passed from the slicer.
2. CompareOrder table doesn't have a relationship with other tables.

[Mariusz]

Hi @Anonymous 

Measures expect the last function to return scalar values and SUMMARIZE return a table function, that's the reason for the error, try the below.

MEASURE NOT IN = 
VAR indexList =
    CALCULATETABLE( 
        DISTINCT ('Order'[CustomerNo]),
        FILTER( 'Order', 'Order'[OrderDate] > [RangeFromDate])
    )
RETURN
    COUNTROWS(
        EXCEPT(
            DISTINCTCOUNT( 'Order'[CustomerNo] ),
            indexList
        )
    )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi,

Run In SQL Server:

select Count(Distinct OrderNo) From [order] where OrderDate <='2020-06-28'
Result
-------
1194

Run In DAX:

Total Order =
CALCULATE(DISTINCTCOUNT('Order'[OrderNo]),FILTER('Order','Order'[OrderDate]=[RangeToDate]))

Result
-------
20

* [RangeToDate] passed dynamically from Slicer (I have changed date format [dd-mm-yyyy] by selecting the measure).

Why the result differs in SQL server and DAX??

References

[Mariusz · Accepted solution]

Hi @Anonymous 

try the below 

NOT 'Order'[CustomerNo] IN indexList

or

NOT ( 'Order'[CustomerNo] IN indexList )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn