# DISTINCT() Across Multiple Fields

• ### Question

• I'm working on a fact table to deliver a scorecard in PowerPivot.  I'm having a problem creating a DAX expression for the calculated column.

I've created a simple version of the problem here.  The requirements for the scorecard are to get the sum of order quantity filtered by  customer or location.  Multiple customers and multiple locations can be included in the filter.  There can also be multiple customers and locations for a single order.

Some sample data is here:

 OrderNum Customer Location Quantity 111 A NY 3 111 A LA 3 222 B FL 5 222 C FL 5 222 D FL 5 333 E NY 7 333 E FL 7 333 F NY 7 333 F FL 7

The following TSQL query will get the correct data:

```WITH    DistinctQuantity
AS ( SELECT
s.OrderNum
,s.Quantity
FROM
dbo.Scorecard s
GROUP BY
s.OrderNum
,s.Quantity
)
SELECT
dq.OrderNum
,SUM(dq.Quantity)
FROM
DistinctQuantity dq
GROUP BY
dq.OrderNum

```

WHERE clauses can be added to the query to filter for multiple customers and locations and it will always return the correct data.

I'd like to create this in PowerPivot using DAX, but I can't find a way to get DISTINCT() or VALUES() to return the unique values for multiple fields.

Friday, December 9, 2011 3:44 PM

• Hi Eric,

Not sure if I am understanding well what you are looking for, but simply using the expression below seems to yield the output you need:

`AVERAGE(Table1[Quantity])`

In your sample data, the Order Qty is always the same for an OrderNum, so If I filter by Customer 'A' and Location 'LA' or 'NY', I get 3 which what the SQL statement gets as well.

However, if I filter by Customer 'B' & 'E', and Location 'FL' I get two rows grouping by Order;  Order 222 has value of 5, and Order 333 has value of 7.  This, again, is the same output as in SQL.

Is this what you need? Unless you have different quanty values per order, this would seem to be Ok....

Javier Guillen
http://javierguillen.wordpress.com/
• Marked as answer by Monday, December 19, 2011 6:45 AM
Friday, December 9, 2011 7:07 PM