2 of 2 people found this helpful
The only way I can think of doing this would be use a parameter. The issue with this though is that you can only choose one item at a time.
I'll have to think more about this one.
2 of 2 people found this helpful
We're dealing with almost exactly the same thing. A parameter would work perfectly except that it doesn't update automatically if new field values are added in the data set. I was wondering if anyone has come up with some other way to do this?
I wouldn't exactly call the below solution elegant or easy, but it works and perhaps it will generate some additional ideas.
The basic idea is to have two rows per row_id and have the exclude filter only apply to one row. Then it's possible to setup a separate filter that shows all row_ids with a count of 1.
To set this up, I
1. Duplicated the data using a UNION ALL, but appended a "_" to the category and sub-category fields in the second SELECT. Without this, the filter will filter both rows. The JET database uses & for concatenation. It looks like
SELECT [Orders$].[City] AS [City], [Orders$].[Customer ID] AS [Customer ID], ... [Orders$].[Product Category] AS [Product Category], ... [Orders$].[Product Sub-Category] AS [Product Sub-Category], ... "primary" as [Z-data] # be sure to add comma at end of previous line FROM [Orders$] UNION ALL SELECT [Orders$].[City] AS [City], [Orders$].[Customer ID] AS [Customer ID], ... [Orders$].[Product Category] & "_" AS [Product Category], ... [Orders$].[Product Sub-Category] & "_" AS [Product Sub-Category], ... "duplicate" as [Z-data] # don't forget the comma at end of prev. line FROM [Orders$]
2a. In the primary scatter plot view, add Z-data to the filter shelf select primary.
2b. Click on the filter box and select "Only Relevant Values" to hide the "_" fields.
This worksheet should now look identical to the previous version.
2c. Right-click on the product sub-category filter and apply it to the "show excluded values" worksheet (or, for this example, I just applied it globally).
3a. Switch to the "show the excluded values" sheet and create a copy of the product sub-category field. Since I'm displaying these values, I'll edit the calculated field to remove the "_". Product Sub-Category (copy):
IF RIGHT([Product Sub-Category], 1) == "_" THEN LEFT([Product Sub-Category], LEN([Product Sub-Category])-1) ELSE [Product Sub-Category] END
3b. Add this new field to the rows shelf, left of the original product sub-category field.
3c. Create a new field called Filtered?
WINDOW_COUNT(ATTR([Product Sub-Category])) == 1
Add this to the filter view. Right-click on the pill, select Edit Table Calculation > Advanced. Move both Product Sub-Category (copy) and Product Sub-Category to the right-hand addressing box. Click OK and select Restarting every Product Sub-Category (copy). Now we're counting the number of product sub-categories. For non-filtered values, there will be 2 rows (name and name_ for the primary and duplicated rows), and for filtered values only 1 (the duplicate name_). Click OK and select TRUE.
3d. Click on the product sub-category pill and unselect Show headers to hide this column.
show excluded outliers_jimw.twbx.zip 220.3 KB
1 of 1 people found this helpful
If you're willing to use a Filter Action instead of a quick filter, you can get the desired results using the technique outlined in Is there any way to store the excluded values(excluded through action filter) in calculated field?. I also came up with a solution using a self-cross product of the filtering dimension and a data blend, but it's complicated.
Thanks Jonathan. I haven't tried your solution yet, but after reading the solution you referred to, I am convinced that this is the correct answer.