# Problem combination DISTINCT  function with ALL function



## davecrt (Jun 3, 2013)

Hi guys. First of all thanks for your attention. Easy question
I'm using this syntax in excel power pivot table
=DISTINCT(ALL(Table2[sedi]) to get as result 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


----------



## Laurent C (Jun 4, 2013)

The DISTINCT function expects a reference to a column, and does not accept column expressions like ALL(Table2[sedi]) or VALUES(Table2[sedi]), ...

However, even if the DISTINCT function accepted an ALL( ...) expression as an argument, your expression would not work as whole, because it would return a set of values, where a scalar result is expected.

Can you tell more about what you want to achieve?


----------



## davecrt (Jun 4, 2013)

First of all thanks Laurent for your interest

I will explain my situation. I have a pivot table with product line as column fields and customers as rows. I want display the sum of the number of "sedi"(whic is the numbers of divisions a customer has) in the values area, without differences in terms of product lines.

My row sales data set present for each customer a different number of sedi in different records(cause a particular situation in the dataset), therefore it can happen that the CustomerX has 3 sedi in one record(with hardware as linked product line) and 2 in another record(with software as linked product line). for instance

customer/number of sedi/productline
customer1/3/hardware
customer2/2/software

In the pivot table which display product line as column fields and customers as row fields i would  that the total sum of unique sedi for each customer would be displayed as the sum of all the unique values showed by the "sedi" variable(therefore in this case 3+2=5) despite the product line subdivision on the columns. I mean, considering the precedent example

Pivot tabel(value field area= populated by sedi number)

column field  Hardware/Software

Row field
Customer1       5          5
Customer2
ecc...

Is it possible?
Regards


----------



## davecrt (Jun 4, 2013)

And Laurent sorry, the initial(wrong) formula in post1 was =sumx(distinct(all(table2[sedi]));table2[sedi])


----------



## Laurent C (Jun 5, 2013)

If I understood correctly, the following configuration is possible:

*customer/number of sedi/productline
*customer1/3/hardware
customer1/2/software
customer1/2/ hardware

... and the expected result for this customer would be 5.

Is it correct?Let me just note that the ALL function when applied to columns returns distinct values. However, in that case, you would loose any filter information - including those related to customers.

What you could do is use the SUMX function in conjunction with a SUMMARIZE expression.

Something like 
SUMX( SUMMARIZE( Table2, [Customer], [Sedi] ) , ... )


----------



## davecrt (Jun 5, 2013)

Correct Lauren. But applying all() to the column field( in this case product line) should not just apply to the column, maintaining the relationship with the row field (in this case the customer field)?

QUOTE=Laurent C;3488213]If I understood correctly, the following 


configuration is possible:

*customer/number of sedi/productline
*customer1/3/hardware
customer1/2/software
customer1/2/ hardware

... and the expected result for this customer would be 5.

Is it correct?Let me just note that the ALL function when applied to columns returns distinct values. However, in that case, you would loose any filter information - including those related to customers.

What you could do is use the SUMX function in conjunction with a SUMMARIZE expression.

Something like 
SUMX( SUMMARIZE( Table2, [Customer], [Sedi] ) , ... )[/QUOTE]


----------



## Laurent C (Jun 5, 2013)

That is why, you should not use  ALL in that case, and I suggested you use summarize instead.


----------



## davecrt (Jun 5, 2013)

CALCULATE(
           SUMX(VALUES(Table2[sedi]);CALCULATE(DISTINCT(Table2[sedi])));
           ALL(Table2[Product line])
   )

This formula given by a guy is working well. However i do not understand how it is possible that CALCULATE accepts as expression parameter a column expression (Distinct) where theoretically it should just accept a calculation function (SUM ecc..)


----------



## Laurent C (Jun 5, 2013)

That is because, your expression is calculated for each row in VALUES(Table2[sedi]), thus the DISTINCT expression only returns a single value every time.

You could actually rewrite the expression as 


> CALCULATE(
> SUMX( VALUES(Table2[sedi]) ; [sedi] );
> ALL(Table2[Product line])
> )



Note that SUMX will only iterate over values of [sedi] in the current selection. Assume your data look like this:
Customer ; Sedi
Customer1; 1
Customer2; 1

The subtotals for Customer1+Customer2 would be 1, with the expression you just provided. With a SUMX + SUMMARIZE expression, the result would be 2.

Pick which one suits you best.


----------



## davecrt (Jun 5, 2013)

*Re: Problem combination DISTINCT  function with ALL function[solved]*

Thnx Lauren. Really a great advice!!! Now i have a real better understanding about this functions combination

Really appreciated!

Davide


Laurent C said:


> That is because, your expression is calculated for each row in VALUES(Table2[sedi]), thus the DISTINCT expression only returns a single value every time.
> 
> You could actually rewrite the expression as
> 
> ...


----------

