Inverted Slicer Results

[pablo2000325]

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. 

[pablo2000325]

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. 

[Anonymous · Accepted solution]

@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

[OwenAuger]

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