locked
DISTINCT() Across Multiple Fields RRS feed

  • 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

Answers

  • 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 Challen Fu Monday, December 19, 2011 6:45 AM
    Friday, December 9, 2011 7:07 PM
    Answerer