# 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
Dave

Monday, June 3, 2013 2:16 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

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 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
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

`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