Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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

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 NoActivityJob IDEvent Timing
Car AStatus AJob A123420/12/2022 0:02
Car AStatus BJob A123420/12/2022 0:02
Car AStandby 20/12/2022 0:55
Car AStatus AJob C223420/12/2022 1:05
Car AStatus BJob C223420/12/2022 2:05
Car BStatus AJob A234520/12/2022 0:08
Car BStatus BJob A234520/12/2022 0:55
Car BStatus CJob A234520/12/2022 1:21
Car BStandby 20/12/2022 1:30
Car BStatus AJob B345620/12/2022 1:45
Car BStatus BJob B345620/12/2022 2:05
Car CStatus AJob B123420/12/2022 0:45
Car CStatus BJob B123420/12/2022 1:05
Car CStandby 20/12/2022 1:35
Car CStatus AJob C124520/12/2022 2:35
Car DStatus AJob B234520/12/2022 0:00
Car DStatus BJob B234520/12/2022 0:45
Car DStatus CJob B234520/12/2022 1:10
Car DStandby 

20/12/2022 1:20


Table 2 
SnaptimeActive Vehicle Count
20/12/2022 1:003
20/12/2022 2:004


sample data snap.JPG

Super User
Super User

hi @limtj 


try to add a column in Table2 like this:

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

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 =
    Table1[Event Timing]>=_snaptime-1/24
        &&Table1[Event Timing]<=_snaptime
VAR _table2 =
SUMMARIZE(_table, Table1[Activity])

it worked like this:


Good Luck.

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!

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. 


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





hi @limtj 

what code do you have for the measure?


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

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 =
    Table1[Event Timing]>=_snaptime-1/24
        &&Table1[Event Timing]<=_snaptime
VAR _table2 =
SUMMARIZE(_table, Table1[Activity])


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.

Super User
Super User

@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))
countx(Filter(values(Table[Car No]), _maxStatus <> "Standby"), [Car No])



Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors