Forum Discussion
UNIQUE function on table with filters
I have a table (fed from SQL). where the table filters are used to select data by date range.
My issue is that using the UNIQUE function on the table data does not appear to take account the table filter(s) and always returns results for the entire unfiltered table. How do I get the UNIQUE function to work only on the filtered table data? HansVogelaar kindly helped me last week with the attached file.
The object was to report on items purchased predominantly by a single customer.
Thanks for any help
Willy
WillSmyth See attached. If that's not what you want, I don't know. Note that I filtered the table to show only items from 2023.
By the way, better to place the SORT(UNIQUE(FILTER(....))) formula in another sheet to avoid it from being hidden in case the filter hides the top rows of the sheet.
WillSmyth You could add a column to the blue table called "filtered" with the following formula:
=AGGREGATE(2,3,[@[TX_DATE]])
That will put a 1 on all rows shown by the filter and 0 on all rows that are not visible.
Then the formula in L2 could be:
=SORT(UNIQUE(FILTER(Table_ExternalData_1[ITEM],Table_ExternalData_1[filtered]=1)))
- Riny_van_Eekelen
I have added the new column FILTERED with the formula =AGGREGATE(2,3,[@[TX_DATE]])
The FILTERED column displays value 0 regardless of the filter set on the table.- I think I have resolved the =AGGREGATE(2,3,[@[TX_DATE]]) formula with all visible rows showing . (assuming hidden rows will be a 0)
However entering the formula in L2 does not appear to work. Would you be able to give example in provided worksheet. Thanks
