topic Re: Inverted Slicer Results in DAX Commands and Tips

Inverted Slicer Results

pablo2000325 — Wed, 03 Jul 2024 22:01:05 GMT

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.

Re: Inverted Slicer Results

OwenAuger — Thu, 04 Jul 2024 01:51:31 GMT

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

Re: Inverted Slicer Results

pablo2000325 — Thu, 04 Jul 2024 03:15:24 GMT

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.

Re: Inverted Slicer Results

Anonymous — Thu, 04 Jul 2024 06:36:05 GMT

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