Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @Anonymous
try the below
NOT 'Order'[CustomerNo] IN indexList
or
NOT ( 'Order'[CustomerNo] IN indexList )
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.
Hi @Anonymous
Can you share your DAX expression?
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.
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
)
)
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
Hi @Anonymous
try the below
NOT 'Order'[CustomerNo] IN indexList
or
NOT ( 'Order'[CustomerNo] IN indexList )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |