Hi There,
Have got a dataset which records the ANPR details of car movements, each time a car arrives on site it records which camera it arrived by and which direction it was going. There are 3 cameras, 1 which is only for exit, 1 only for entrance and 1 which can be either entrance or exit. I am looking to calculate the cars that are on site at any particular time. I have measures which show the count of car movements through each camera (and direction). But the data is stored in a SQL database so am using direct query and need to be able to see what the last time a particular car went through a camera and which one it was so I can work out which cars are on site, Ideally i would then like to be able to visualise this over time so we can the number of cars on site throughout the day and how that varies and also show which cars are on site at any time (which I can then analyse by make/,model/year of manufacture etc, have a got an API that can pull the appropriate details)
Have been experimenting with Calculate(CountAX(Car Reg)) but have struggled to think about the filters that pull the correct data.
Data Structure is
Access_date (Date Format)
AccessDateTime (Time Format)
Camera (3 Different Values)
CarReg (Car Registration that ANPR has captured)
Createdon (Date/Time Format)
Direction (Forward/Reverse/Unknown)
Measures already built
Farm Entrance = CALCULATE(COUNTA(anprData[CarReg]),FILTER(anprData,anprData[camera]=“FARMANPR”&&anprData[Direction]=“Forward”))
Entered Resort Barrier = CALCULATE(COUNTA(anprData[CarReg]),anprData[camera]=“ArrivalsEntryANPR”)
Farm Exit = CALCULATE(COUNTA(anprData[CarReg]),FILTER(anprData,anprData[camera]=“FARMANPR”&&anprData[Direction]=“Reverse”))
Left Resort Barrier = CALCULATE(COUNTA(anprData[CarReg]),anprData[camera]=“VisitorExit”)
In Out Movement = [Farm Entrance] + [Entered Resort Barrier] - [Left Resort Barrier] - [Farm Exit]
Any help would be greatly appreciated
Thanks
Simon