Forum Discussion

dgdgdg122db's avatar
dgdgdg122db
Helper II
4 years ago
Solved

variables virtual tables

Hello there, I am trying to count the number of orders for the customers who has the total order value above 50K. My DAX looks like below, I just do not how to continue. How to pass the "50orders" ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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.

dgdgdg122db's avatar
dgdgdg122db
Helper II
4 years ago

Could you please tell me why the Calculate is needed in this section : CALCULATE ( SUM( Orders[Order Value EUR]) ) > 50000 ?

 

 

bcdobbs's avatar
bcdobbs
Community Champion
4 years ago

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.