Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Optimising a dual-filter expressiong

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?

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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
    ))

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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
      ))