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-0...
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

     

Mariusz's avatar
Mariusz
Community Champion
6 years ago

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

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