Power Pivot- Error in the Combination of Distinct function with All functi

[davecrt]

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

 

[SirJB7]

Hi, davecrt!

Does this help?

http://office.microsoft.com/en-us/excel-help/distinct-function-dax-HA102838209.aspx

Regards!

 

[NARAYANK991]

Hi Dave ,


First , I have absolutely no idea of Powerpivot. I have never worked with it.


If you see this link :


http://social.technet.microsoft.com/wiki/contents/articles/680.powerpivot-dax-filter-functions.aspx


there is a section , from which I will quote :

[pre]

ALL( {<table> | <column>[, <column>[, <column>[,…]]]} )

Table. The table that you want to clear filters on.

Column. The column that you want to clear filters on.

The argument to the ALL function must be either a reference to a base table or a reference to a base
column. You cannot use table expressions or column expressions with the ALL function.

[/pre]
The last sentence is probably the key :

You cannot use table expressions or column expressions with the ALL function ; is your Table2 or Table2[sedi] a base table or a base column ?


By expression , I assume they mean a calculated or derived table or a calculated column , unlike a base table or base column.


What is your Table2 or Table2[sedi] ?


Narayan

 

[davecrt]

@SirJB7 i have already read the Microsoft post, but unfortunately while they indicate that the DAX combination


DISTINCT(ALL( Table[Column])) should work fine for the above mentioned scope, in my case get the error i reported


@Naraya Table2=base table

Table2[sedi]=base column


So they should work fine. At leat considering the indication Microsoft give in the post indicated by @Sirjb7

 

[NARAYANK991]

Hi Dave ,


In that case , all I can suggest is that if you can upload your file , anyone who has Powerpivot on their computer can check what the problem is. I do not have it.


Narayan

 

[davecrt]

http://connect.microsoft.com/SQLServer/feedback/details/733970/dax-documentation-distinct-remark-is-incorrect


I have found also this post from Microsoft, but still they do not give a solution to the issue..

 

[davecrt]

Mmmm.. Gotta the same error with this syntax


=sum(DISTINCT(Table2[sedi]))


The SUM function only accepts a column reference as an argument.


It is really strange..distinct should return a column so why the SUM function does not accept it as parameter??