Answered by:
Power Pivot Error in the Combination of "Distinct" function with "All" functi
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
DaveMonday, 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/sumxthe5pointpalmexplodingfxntechnique/
The author explain very detail.
Best regards
 Proposed as answer by Ed Price  MSFTMicrosoft employee Thursday, August 22, 2013 6:59 PM
 Marked as answer by Ed Price  MSFTMicrosoft employee Monday, September 16, 2013 6:36 AM
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 filtersTuesday, June 4, 2013 12:26 PM

Guys any idea? i'm really getting crazy about this issue... ThnxTuesday, 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 hardware1
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/sumxthe5pointpalmexplodingfxntechnique/
The author explain very detail.
Best regards
 Proposed as answer by Ed Price  MSFTMicrosoft employee Thursday, August 22, 2013 6:59 PM
 Marked as answer by Ed Price  MSFTMicrosoft employee Monday, September 16, 2013 6:36 AM
Wednesday, June 5, 2013 1:34 PM