Restricting data with filters and where conditions

In the Using projection to combine data from different tables recipe, we stated that there is no WHERE clause in DAX. If this type of restriction is to be applied to a result set, it must be defined by a query, and this is the focus of this recipe. Here, we seek to answer the question:

Which customers had sales in 2008 and what was their sales value (for that year)?

Getting ready

The model used in this recipe is the sales data that has been used in this chapter (see the workbook Model.xlsx, which is available from online resources at http://www.packtpub.com).

How to do it…

This recipe examines two queries. Firstly, listing customers and their sales value in 2008, and secondly, listing customers that had sales in 2008 with their sales value.

1. Create a new query to show the 2008 sales values of customers. The query is as follows:

   define measure 'Resellers'[Sales] = calculate('Reseller Sales'[USD Gross Sales], 'Dates'[Year]=2008)
   
   evaluate(
     addcolumns...