cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 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

8 REPLIES 8
Super User

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.

Frequent Visitor

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.

Super User

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.

Frequent Visitor

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

Super User

hi @limtj

what code do you have for the measure?

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

Frequent Visitor

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)```

Super User

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

@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])

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors