Comparing to Filtered Table

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