locked
Power Pivot- Error in the Combination of "Distinct" function with "All" functi RRS feed

  • Question

  • Hi guys. First of all thanks for your attention. Easy question

    I'm using this syntax in excel power pivot table

    =sum(DISTINCT(ALL(Table2[sedi])) to get as result the sum of all the unique values that the field "sedi" displays, despite filters applied in the corresponding pivot table

    But i got as result "The DISTINCT function expects a column reference expression for argument '1', but a table expression was used."

    Any tips???

    thnx
    Dave

    Monday, June 3, 2013 2:16 PM

Answers

  • Hi Davidcrt

    Firstly, English is not my native language. I am Excel guy. :-). I just learn DAX in this few months.

    I read a lot of row context and filter context of DAX formulas. I understand it by my way. But i can't express it to you by English.

    I try to explain my formulas. Maybe, it is not clearly with you.

    CALCULATE(

            SUMX(VALUES(Table2[sedi]),CALCULATE(DISTINCT(Table2[sedi]))),

            ALL(Table2[Product line])

    )

    DISTINCT(Table2[sedi]) will make a field with a distinct values of sedi and CALCULATE will wrap this field as parameter of VALUES(Table2[sedi]) and SUMX will sum this 2 column like SUMIF formulas of excel

    You can read below link. That maybe help you more clearly.

    http://www.powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/

    The author explain very detail.

    Best regards

    Wednesday, June 5, 2013 1:34 PM

All replies

  • You can use this formulas:

    =SUMX(VALUES(Table2[sedi]),CALCULATE(DISTINCT(Table2[sedi])))

    
    
    

    • Edited by CDzung Tuesday, June 4, 2013 3:58 AM
    Tuesday, June 4, 2013 3:57 AM
  • Hi CDzung, thnx for your reply but the final result is not optimal.

    Cause i have displayed on the pivot table columns the Product Lines, i got as result that the field sedi is not the same for each product line as result in the value area. I mean  i have this situation:

                                     Columns

                                     Hardware      Software

    Rows Customer1      Values=sedi(number of divisions)

    At the moment i get different result for each customer(rows) for each product line(columns)

    While i would get the unique number of sedi, not being influenced by the product line filter applied in the pivot table. In the example i should get 4 as number of sedi both in hardware and software(is the unique number of sedi for the customer despite the product line), while at the moment i get 4 for hardware and 1 for hardware(which is the unique number of sedi for the customer but filtered by product line)

    Tuesday, June 4, 2013 12:15 PM
  • I mean, the problem in my opinion concerns the ALL function that must be nested in some way. Without it, it looks like the distinct values are influenced by the pivot table filters
    Tuesday, June 4, 2013 12:26 PM
  • Guys any idea? i'm really getting crazy about this issue... Thnx
    Tuesday, June 4, 2013 3:30 PM
  •  Hi Davidcrt

    I don't see your data structure. As my understanding,if your table2 also have 2 columns for customer and Product line. you can change the formulas to:

    
    
    
    
    
    
    
    =SUMX(VALUES(Table2[Customer]),CALCULATE(DISTINCT(Table2[sedi])))
    Wednesday, June 5, 2013 6:55 AM
  • no Cdzung. i have in the pivot table

    2 columns for product line field (hardware/software)

    and a number of rows for customer field (customer1/customer2 ecc..)

    each customer can have several unique values for the "sedi" field (for instance customer 1 can have 1 and 2 as values for the sedi field, customer 2 1, 4 and 5 ecc..)

    for instance, considering the following records:

    customer/product line/sedi

    customer1 -hardware-1

    customer1 -software -2

    customer 1 -hardware- 2

    ecc..

    What i would get in the values area is the sum of the unique values of the "sedi" field for each customer, unfiltering the column differentiation per product line. Which mean, considering the example for customer 1

    I shoud have in the customer 1 corresponding row in the pivot table the value 3(sum=1+2) both in the hardware column and software column

    Wednesday, June 5, 2013 10:23 AM
  • Hi Davidcrt

    I have made a sample to share on skydrive. Hopefully, that will help you.

    My account can not insert hyperlink. Please use below code clock to download

    https://skydrive.live.com/#cid=94B99C319E63236A&id=94B99C319E63236A%21105

    
    
    
    Wednesday, June 5, 2013 10:59 AM
  • Hi CDzung. YEa, it works!

    however some doubts...CALCULATE function isn't obliged to contain a CALCULATION in the expression parameters?(the first one)??

    On the contrary you put a column (DISTINCT(tABLE2[SEDI])) in your expression. Why is it possible??

    Wednesday, June 5, 2013 12:32 PM
  • HEMA

    Distinct does not accpet ALL as paramater, cause it  expects a reference to a column, and does not accept column expressions like ALL(Table2[sedi]) or VALUES(Table2[sedi]), ...

    Wednesday, June 5, 2013 12:39 PM
  • Hi Davidcrt

    Firstly, English is not my native language. I am Excel guy. :-). I just learn DAX in this few months.

    I read a lot of row context and filter context of DAX formulas. I understand it by my way. But i can't express it to you by English.

    I try to explain my formulas. Maybe, it is not clearly with you.

    CALCULATE(

            SUMX(VALUES(Table2[sedi]),CALCULATE(DISTINCT(Table2[sedi]))),

            ALL(Table2[Product line])

    )

    DISTINCT(Table2[sedi]) will make a field with a distinct values of sedi and CALCULATE will wrap this field as parameter of VALUES(Table2[sedi]) and SUMX will sum this 2 column like SUMIF formulas of excel

    You can read below link. That maybe help you more clearly.

    http://www.powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/

    The author explain very detail.

    Best regards

    Wednesday, June 5, 2013 1:34 PM