Filter overide with && as well as ||

[EWBWEBB]

Hi 

I am trying to create a list of distinct dates a customers have entered a store by date but only for a specified set of locations.

I seem to be running into an error when I try to add more than one location.

My data is laid out as follows

CustomerID	CustomerName	PrimaryMembership	AccessRule	EntryDate	Type	Location
12345	Bob	Full	2DAF	01/01/2022	Cycle	Norwich
12345	Bob	Full	2DAF	02/01/2022	Cycle	Norwich
12345	Bob	Full	2DAF	03/01/2022	Cycle	Norwich
12345	Bob	Full	2DAF	04/01/2022	Cycle	Norwich
12345	Bob	Full	2DAF	05/01/2022	Cycle	Norwich
23456	Jo	Full	2DAF	01/01/2022	Car	London
23456	Jo	Full	2DAF	02/01/2022	Car	London
23456	Jo	Full	2DAF	03/01/2022	Car	London
23456	Jo	Full	2DAF	04/01/2022	Car	London
23456	Jo	Full	2DAF	05/01/2022	Car	Norwich
34567	Jane	Full	2DAF	01/01/2022	Cycle	Clacton
78910	Anne	Full	2DAF	01/01/2022	Car	Norwich
89101	Terry	Full	2DAF	01/01/2022	Cycle	London
91011	Michael	Full	2DAF	01/01/2022	Cycle	Norwich
10111	Steve	Full	2DAF	01/01/2022	Cycle	London
11121	Alyssa	Full	2DAF	01/01/2022	Car	Clacton
12131	Martin	Full	2DAF	01/01/2022	Car	Clacton
13141	Robin	Full	2DAF	01/01/2022	Car	Norwich
15161	Dave	Full	2DAF	10/01/2022	Car	Cambridge

Using the following I end up with a resulting table that on the face of it seems correct.

DISTINCT(
	SELECTCOLUMNS(
		FILTER(
			Entry,
			Entry[EntryDate] <> BLANK()
			--&& Entry[CustomerID] = 15161
			&& Entry[Location] ="Norwich" || 
				Entry[Location] = "London" || 
				Entry[Location] = "Clacton"
				),
				"_EntryDate",[EntryDate]
			)				
		)

However

If remove the comment from 

&& Entry[CustomerID] = 15161

I would expect the result to be an empty table as they don't have an entry in those locations but I seem to be getting the whole list regardless.

If I ammend the code to just have a single location then i get the blank table as expected.

I'm assuming something is going awry with the OR element of this but I can't figure out why.

I'm using the ID as a test, I will comment out and filter by ID in the report but I have 160000 rows of data so need to do a little sense checking.

Help!

[Shaurya · Accepted solution]

Hi @EWBWEBB,

The OR has to be applied only within the condition for checking the location. Everything else will be checked with AND. Brackets will fix your code:

DISTINCT(SELECTCOLUMNS(FILTER(Entry,
(Entry[EntryDate] <> BLANK())
&& (Entry[CustomerID] = 15161)
&& (Entry[Location] = "Norwich"
|| Entry[Location] = "London"
|| Entry[Location] = "Clacton")),"_EntryDate",Entry[EntryDate]))

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Previous Forecasts

[EWBWEBB]

Spot on, that worked like a charm.

Thank you