How to do a reverse filter (display what's been excluded by the filter)

[alex139]

This might sound trivial, but here it goes...

I have a query as data source with a date field and a company name field (there are more fields in the query but only these two are relevant to what I want to acomplish).

I want a dashboard with three visuals:

1 visual to display the date range using the date field as source (DONE - I created it and I can pick the date range);

1 visual to display the companies using the company field as source, which is supposed to get filtered based on the date range I select in the date visual I described above (DONE - it works);

1 visual to display the companies that DO NOT fall in that date range (don't know how to do it);

The most important thing here is that I don't want to use the report level filter.

I know I have the options to use the IS NOT operator and pick a date range from there but I don't want to use that option - I want to have that third visual filter based on the date range I pick on the date visual.

I am not sure what other options I have.

Thanks in advance for your help.

Alex

[MattAllington]

Try this measure

count of outside date range = calculate(countrows(table),filter(all(table),table[date] >=max(table[date]) || table[date] <= min(table[date]))

Depending on the visual, this will return the customers outside the range. So if you put customers and thgthis measure on a table, it will show only customer names outside the range

[alex139]

Hi Matt,

Thanks for taking the time to look into my issue.

I used the measure you've suggested but it returned the same companies I have in the other visual.

If you have time, can you explain the logic behind the measure? Why did you choose to use a count for companies outside the date range?

I gave it some thought and I created a separate page with two parameters (range start date [RSD] and rang end date [RED]).

Then I duplicated my query and used my two parameters in the Date filter (Date is before RSD OR Date is after RED).

So far so good - I get the results I want, but I don't like the solution as it's not very elegant - I have to use the Edit Parameters option to requery.

It would be great if I could get the RSD and RED from the timeline visual - I want to modify the parameters based on the timeline visual output - is there a way I can get to the values output by the timeline (might sound stupid what I am asking).

Thanks for your help!

Alex

[FarhanAhmed1984]

Create another measure and Try use ALLEXCEPT filter in that measure which will evaluate value for DATES which are not selected.

[MattAllington]

---

@FarhanAhmed1984 wrote:

Create another measure and Try use ALLEXCEPT filter in that measure which will evaluate value for DATES which are not selected.

---

That wont work.  ALLEXCEPT is used to remove filter context. Using it in this scenrio will ignore any filters on all dates.

[MattAllington]

If you post a sample workbook I will take a look.  Set it up with some sample data and add the DAX and visuals you have.  The issue with helping people on forums (and I help a lot) is that it takes 80% of the effort to set up sample data and 20% to provide answers.  So if you do the first 80%, I will help with something that works.

Regarding COUNT, I could have used anything.  You need a DAX measures that returns a value if the customer should be displayed, and returns a blank if the customer should not be displayed.  Then put this measure in a visual and it should all work - as long as the measure is correct of course.  

[Sean]

---

@MattAllington wrote:

If you post a sample workbook I will take a look.  Set it up with some sample data and add the DAX and visuals you have.  The issue with helping people on forums (and I help a lot) is that it takes 80% of the effort to set up sample data and 20% to provide answers.  So if you do the first 80%, I will help with something that works.

Can we pin this statement to the top of the posts!