Optimising a dual-filter expressiong

[Anonymous]

The following aims to identify "clusters" of incidents, defined as occurring with a given radius and a given time period.

Cluster Time Test =
CALCULATE(
DISTINCTCOUNT(
'SERVICE REQUESTS'[Master Details Summary.Record ID]),
FILTER(
'SERVICE REQUESTS',
ABS('SERVICE REQUESTS'[ReceivedDTG]-EARLIEST('SERVICE REQUESTS'[ReceivedDTG]))<0.0416
)
)

This is the cluster time (detects all occurrences within an hour before/after a given row)
 
Cluster Calculation Test =
CALCULATE(
DISTINCTCOUNT(
'SERVICE REQUESTS'[Master Details Summary.Record ID]),
FILTER(
'SERVICE REQUESTS',
(
SQRT(
('SERVICE REQUESTS'[AbsLatitude]-EARLIEST('SERVICE REQUESTS'[AbsLatitude]))^2
+
('SERVICE REQUESTS'[AbsLongitude]-EARLIEST('SERVICE REQUESTS'[AbsLongitude]))^2
)
)<0.001
))

This is the cluster location - detects within a given circular radius of the row.

Both filters work fine individually, but when I combine them, there seems to be some sort of sub-optimisation and thus they never complete (stick at "Working on it"):

Cluster Calculation Test =
CALCULATE(
DISTINCTCOUNT(
'SERVICE REQUESTS'[Master Details Summary.Record ID]),
FILTER(
'SERVICE REQUESTS',
(
SQRT(
('SERVICE REQUESTS'[AbsLatitude]-EARLIEST('SERVICE REQUESTS'[AbsLatitude]))^2
+
('SERVICE REQUESTS'[AbsLongitude]-EARLIEST('SERVICE REQUESTS'[AbsLongitude]))^2
)
)<0.001
),
FILTER(
'SERVICE REQUESTS',
ABS('SERVICE REQUESTS'[ReceivedDTG]-EARLIEST('SERVICE REQUESTS'[ReceivedDTG]))<0.0416
)
)
Any tips on optimising?

[Anonymous]

Hi @Anonymous 

When you apply multiple filters for 'SERVICE REQUESTS'[Master Details Summary.Record ID] , you should make sure that there is data that can meet your filter criteria at the same time . Otherwise the returned data will be empty .

For example :

Original Data

Then I create two measures to filter the data I want

(1) Measure = CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Name]="A"),FILTER('Table','Table'[Date]=2016))

(2) Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Name]="A"),FILTER('Table','Table'[Date]=2017))

Now you can see the difference. The two filters in the first formula do not cross data, so the returned result is empty .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Morning,

Thankyou for this advice! Having two filters did not work for me, but a slight revision of the code did:

Cluster Calculation Test =

CALCULATE(

DISTINCTCOUNT(

'SERVICE REQUESTS'[Master Details Summary.Record ID]),

FILTER(

'SERVICE REQUESTS',

(

SQRT(

('SERVICE REQUESTS'[AbsLatitude]-EARLIEST('SERVICE REQUESTS'[AbsLatitude]))^2

+

('SERVICE REQUESTS'[AbsLongitude]-EARLIEST('SERVICE REQUESTS'[AbsLongitude]))^2

)

)<0.001

&&

ABS('SERVICE REQUESTS'[ReceivedDTG]-EARLIEST('SERVICE REQUESTS'[ReceivedDTG]))<0.0416

))