DAX calculated column or measure for distinct count based on 2 tables

[limtj]

Hi, I have tried searching the forum for related queries but cant seem to find one that works. Will really appreciate if anyone can help.

I am looking to calculate the distinct number of vehicles that are ACTIVE for every hourly time stamp. 

I have Table 1 which has fields Car No, Activity, Job ID, Event Timing. For every unique Job ID, there are several job status (Activity) at different timing. If these vehicle has a job status tagged to it durring the event timing, it means that the vehicle is ACTIVE.  The vehicle is considered INACTIVE if it is under "Standby" mode. Whenever the vehicle is issued a new job, it have another new Job ID. 

Now I want to calculate at every hourly snap, how many vehicles are ACTIVE (i.e. not under Standby mode). I have Table 2, which has a column of daily hourly snap at 0000hr, 0100hr, 0200hr, etc.

How do i get the Active Vehicle Count column as a column in Table 2? As can be seen from my manual table, Active Vehicle Count for Snaptime 20/12/2022 0100hr is 3 (Car B, C and D) because at 0100hr, these 3 vehicles are ACTIVE (not on standby). 

Would really appreciate any help, thanks!!

Table 1
Car No	Activity	Job ID	Event Timing
Car A	Status A	Job A1234	20/12/2022 0:02
Car A	Status B	Job A1234	20/12/2022 0:02
Car A	Standby		20/12/2022 0:55
Car A	Status A	Job C2234	20/12/2022 1:05
Car A	Status B	Job C2234	20/12/2022 2:05
Car B	Status A	Job A2345	20/12/2022 0:08
Car B	Status B	Job A2345	20/12/2022 0:55
Car B	Status C	Job A2345	20/12/2022 1:21
Car B	Standby		20/12/2022 1:30
Car B	Status A	Job B3456	20/12/2022 1:45
Car B	Status B	Job B3456	20/12/2022 2:05
Car C	Status A	Job B1234	20/12/2022 0:45
Car C	Status B	Job B1234	20/12/2022 1:05
Car C	Standby		20/12/2022 1:35
Car C	Status A	Job C1245	20/12/2022 2:35
Car D	Status A	Job B2345	20/12/2022 0:00
Car D	Status B	Job B2345	20/12/2022 0:45
Car D	Status C	Job B2345	20/12/2022 1:10
Car D	Standby		20/12/2022 1:20

Table 2
Snaptime	Active Vehicle Count
20/12/2022 1:00	3
20/12/2022 2:00	4

[FreemanZ]

hi @limtj 

try to add a column in Table2 like this:

Active Vehicle Count2 = 
VAR _table = 
FILTER(
    Table1,
    Table1[Event Timing]>=Table2[Snaptime]-1/24
        &&Table1[Event Timing]<=Table2[Snaptime]
)
VAR _table2 =
SUMMARIZE(_table, Table1[Activity])
RETURN
COUNTROWS(_table2)

i tried and it worked like this:

you may also plot a table visual with the Snaptime column and a measure like this:

Active Vehicle Count3 = 
VAR _snaptime = MAX( Table2[Snaptime])
VAR _table =
FILTER(
    Table1,
    Table1[Event Timing]>=_snaptime-1/24
        &&Table1[Event Timing]<=_snaptime
)
VAR _table2 =
SUMMARIZE(_table, Table1[Activity])
RETURN
COUNTROWS(_table2)

it worked like this:

Good Luck.

[limtj]

hi @FreemanZ 
thanks much for the recommended solution. I have a few further queries. Sorry I am not so well-versed with DAX. 
1. for the records with Activity as "Standby" i would like to disregard them as they are not considered ACTIVE when they are under this mode. Which part of your code should i include this portion as noted that your recommended code did not include this portion.

2. For the custom column formula, am i counting the distinct number of Car No for every hourly snap when i SUMMARIZE by Table1[Activity]?

3. Can i also ask which method process faster? I am trying out your custom column code and it has been running for 30 mins and still have yet to produce the output. for reference, i have ~15mil rows for Table1 and it is growing everyday. 

Thanks much for your advice!

[FreemanZ]

Hi @limtj 

1) see below.

2) yes, distinct count. So disregarding "Active" in question 1 is not relevant to the needed calculation. 

3) measures is better. Actually in DAX, custom column is used only when a measure is not possible, like when you need the added column to slice/filter the data. But if you have the chance to add the custom column in your datasource or in PQ, do so. 

[limtj]

Unfortunately i am not able to find out what exactly is wrong.

[FreemanZ]

hi @limtj 

what code do you have for the measure?

p.s. if you need further suggestion, please consider @ me in the reply. 

[limtj]

hi @FreemanZ sorry missed out the @ on previous reply. 

i have used the code that you have provided for measure (below). is there anything that i may be doing wrong?

Active Vehicle Count3 = 
VAR _snaptime = MAX( Table2[Snaptime])
VAR _table =
FILTER(
    Table1,
    Table1[Event Timing]>=_snaptime-1/24
        &&Table1[Event Timing]<=_snaptime
)
VAR _table2 =
SUMMARIZE(_table, Table1[Activity])
RETURN
COUNTROWS(_table2)

[FreemanZ]

hi @limtj 

1) you pasted my code, what code do you have after changed your table and column names?

2) Also please ensure the data type of the DateTime or Snaptime needs to be Date/Time.

[amitchandak]

@limtj , Try a measure like

measure =

var _max = max(Table2[Snaptime] )
var _maxStatus = calculate(lastnonblankvalue( Table[Event Timing]< max(Table[Activity])), filter(allselected(Table), Table[Car No] = max(Table[Car NO]) &&Table[Event Timing] <= -Max))
return
countx(Filter(values(Table[Car No]), _maxStatus <> "Standby"), [Car No])