Forum Discussion
variables virtual tables
Sorry misunderstood your requirement. Hard to test without some data but I think you want to do something like:
#Orders (Buyers OrderValue>50k) = VAR Customers50k = FILTER ( VALUES ( 'Orders'[CustomerID] ), CALCULATE ( SUM( Orders[Order Value EUR]) ) > 50000 ) RETURN CALCULATE ( DISTINCTCOUNT ( Orders[Sales document] ), Customers50k )Effectively generate the list of CustomerIds and then use it as a filter inside calculate.
Have you tried COUNTROWS ( order50K )
Yes and that is incorrect, it will only count the number of customers, (with a total sales amount >50K) not the number of orders.
Sorry misunderstood your requirement. Hard to test without some data but I think you want to do something like:
#Orders (Buyers OrderValue>50k) = VAR Customers50k = FILTER ( VALUES ( 'Orders'[CustomerID] ), CALCULATE ( SUM( Orders[Order Value EUR]) ) > 50000 ) RETURN CALCULATE ( DISTINCTCOUNT ( Orders[Sales document] ), Customers50k )Effectively generate the list of CustomerIds and then use it as a filter inside calculate.
Thank you very much bcdobbs
Could you please tell me why the Calculate is needed in this section : CALCULATE ( SUM( Orders[Order Value EUR]) ) > 50000 ?
I'll try!
CALCULATE does a number of things in DAX. It can add/remove filters but it also forces "context transition". This is where a row context gets moved into the filter context for a calculation.
In the code FILTER iterates over each row in the list of CustomerIds. For each row (customer is) we need to calculate total sales so that rows customer id needs to be moved into the filter context. If it wasn't there you'd get the same value for every customer id.