Comparing to Filtered Table

[Diego-mx]

I have one table with business locations: 

location_name, open_date, close_date

I have a calendar table that gets filtered via a slicer.
I want to count how many locations were open in a given window of time.  

I tried the following: 

OpenLocations = CALCULATE( COUNT( [location_name]), 
   FILTER( Locations, 
       [open_date] <= MIN( Calendar[a_date] ) & (
         [close_date] >= MAX( Calendar[a_date] ) || ISBLANK( [close_date] )
))

However, this doesn't work because the tables are not related.  
Thank you for your help. 

[Ashish_Mathur]

Hi,

This isn't a difficult problem to solve.  Share some data.

[Diego-mx]

My dashboards are very convoluted already.  For my next post, I'll try to come up with a toy dataset. 

This one got solved alright. 
Thanks though. 

[OwenAuger · Accepted solution]

Hi @Diego-mx

"Events in progress" is the generic name for the type of measure you are wanting to create here. From your description, I take it that you want to count Locations that were open on at least one day in the filtered period.

1. First of all, if we just try to fix your current measure, there is a slight logical error: we actually want Locations that open on or before the Max filtered date, and close on or after the Min filtered date. Also, just to be safe I suggest using DISTINCTCOUNT, though it won't matter if Locations appear at most once. A corrected measure would be:
   

   OpenLocations =
   CALCULATE (
       DISTINCTCOUNT ( Locations[location_name] ),
       FILTER (
           Locations,
           Locations[open_date] <= MAX ( 'Calendar'[a_date] )
               && (
                   Locations[close_date] >= MIN ( 'Calendar'[a_date] )
                       || ISBLANK ( Locations[close_date] )
               )
       )
   )

2. The above measure however may not perform well in a large model, as you are filtering the entire Locations table.
   Based on this paper on SQLBI (page 27) a better-performing measure would be:
   

   OpenLocations v2 = 
   VAR SelectedDates =
       VALUES ( 'Calendar'[a_date] )
   RETURN
       CALCULATE (
           DISTINCTCOUNT ( Locations[location_name] ),
           GENERATE (
               SUMMARIZE ( Locations, Locations[open_date], Locations[close_date] ),
               INTERSECT (
                   DATESBETWEEN ( 'Calendar'[a_date], Locations[open_date], Locations[close_date] ),
                   SelectedDates
               )
           )
       )

3. Another option that you may want to consider is restructuring your Locations table according to this article on SQLBI, so that there is a row per date that each Location is open, with a single date column.
   
   

Regards,

Owen

[Diego-mx]

This did it.  Thanks. 

In terms of the logic, I must have been looking for locations that were open for the whole date range.  Depending on the focus, I can see how this is useful as well. 
For longer periods, it might be worth considering the proportion of times that they were open via the repeated dates on each location, which can be more easily done with the table you suggested. 

The thing with the measure as I had put it and as on your first suggestion, is that it doesn't respond to the filters from Calendar table.  And without having the list of dates, it doesn't make sense to make a relationship there. 

In any case, the solution with GENERATE, SUMMARIZE, and INTERSECT did work. 
I must say that coming from the R-dplyr world, I still need to understand what these functions' role is when creating the filters.  But then that makes a good homework problem.  

Cheers from Mexico. 

[OwenAuger]

Glad to hear one of the options worked.

For me options 1 & 2 gave the same result in a test model but that might not always hold.

I should have checked whether you wanted locations partly or completely open over the filtered dates as well 🙂