Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
89 | |
79 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
115 | |
109 | |
74 | |
69 |