Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to Use NOT IN Query in PowerBI DAX

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

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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

     

6 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Mariusz
      Community Champion
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.