ALL function in virtual table is not removing duplicates

[hideakisuzuki01]

Hi,

I cannot seem to figure out this so I need help.

I have this simple table.

Order Number	Qty	UnitPrice	Sales
A	1	80	80
B	1	80	80
C	3	200	600
D	3	200	600
E	5	400	2000

When I create a calculated table with the below DAX code, 

Table1 = FILTER(

ALL(

'Sales'[Qty],

'Sales'[UnitPrice]

),

'Sales'[Qty]*'Sales'[UnitPrice] > 100)

I get this simple table.

But if I create a measure with the DAX code (which includes the above DAX code), 

TransactionsHigherThan$100　 =

CALCULATE( SUM('Sales'[SalesAmount]),

FILTER(ALL(

'Sales'[Qty],

'Sales'[UnitPrice] ),

'Sales'[Qty]*'Sales'[UnitPrice] >= 100)

)

If I show the value of the measure using Card or table, then the total I get is $3200, but shouldnt that be $2600 ?

because the ALL function is supposed to remove the duplicates (order number C and D and the duplicates do get removed in the above calculated table).

Please let me know if you need clarification.

[CNENFRNL]

The first ALL() returns a physical table, as you see; whereas the second ALL() is embedded in FILTER() and FILTER() returns a filter for CALCULATE(). That's to say, any rows pass through the filter will be used for calculation.

[hnguy71 · Accepted solution]

@hideakisuzuki01 , hehehe, the ALL function kinda doesn't work like that.

Try this as a measure instead:

@Sales = 
VAR _Expression = FILTER(SUMMARIZECOLUMNS(Sales[Qty], Sales[UnitPrice]), Sales[Qty] * Sales[UnitPrice] >= 100) 

RETURN

SUMX(_Expression, [Qty] * [UnitPrice])