Forum Discussion
Solved
ALL function in virtual table is not removing duplicates
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.
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])
2 Replies
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])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.
