Forum Discussion
Inverted Slicer Results
I thought this would be rather straightforward but it's not been at all. I have a slicer filtering on Category in a table which functions just fine. Let's call this table1. I'm trying to understand how I can create a dynamic table (table2) which contains all the items filtered from table1 using the slicer.
Been researching this all day and I found the following:
table2 =
FILTER(
ALL('table1'),
NOT('table1'[Category] IN VALUES('table1'[Category]))
)
When I apply the slicer this doesn't return anything in table 2. Table2 is empty.
How can I return the inverted results of the slicer to populate table2 with those results unless there is another way.
Thanks in advance.
OwenAuger Thanks for your contribution on this thread.
Hi pablo2000325 ,
When creating a calculated table, it’s important to note that the data remains static regardless of filter or slicer options, as OwenAuger mentioned. To implement this, follow these guidelines:
1. Create a category dimension table (DO NOT create a relationship with the fact table).
Categories = VALUES('table1'[Category])2. Create a slicer and place the field [category] in the above dimension table on the slicer.
3. Create a measure as below
Mesasure = VAR _categories = ALLSELECTED ( 'Categories'[Category] ) RETURN CALCULATE ( MAX ( 'table1'[hour] ), FILTER ( 'table1', NOT ( 'table1'[Category] IN _categories ) ) )4. Create a table visual, put the field 'table1'[Category] and the above measure onto it
Best Regards
3 Replies
- OwenAuger
Super User
Hi pablo2000325
A calculated table like table2 does not respond to filters in a report.
It is processed as part of the semantic model refresh (in an unfiltered context), and from the report's point of view it is a static table.
Because all rows of table1 are visible in an unfiltered context, table2 is empty.
However, a table expression like table2 can be used within a measure, as a step to compute some other result.
What is the end result you are wanting based on table2?
I can probably suggest some options depending how you want to use it.
Regards
So table1 has development hours allocated per user and what I want to do is create a table2 which summarizes those development hours for users NOT in the slicer. The slicer currently adds the development hours based upon what is sliced so no issue with table 1. I hope this makes sense and thanks again.
OwenAuger Thanks for your contribution on this thread.
Hi pablo2000325 ,
When creating a calculated table, it’s important to note that the data remains static regardless of filter or slicer options, as OwenAuger mentioned. To implement this, follow these guidelines:
1. Create a category dimension table (DO NOT create a relationship with the fact table).
Categories = VALUES('table1'[Category])2. Create a slicer and place the field [category] in the above dimension table on the slicer.
3. Create a measure as below
Mesasure = VAR _categories = ALLSELECTED ( 'Categories'[Category] ) RETURN CALCULATE ( MAX ( 'table1'[hour] ), FILTER ( 'table1', NOT ( 'table1'[Category] IN _categories ) ) )4. Create a table visual, put the field 'table1'[Category] and the above measure onto it
Best Regards
