Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead 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

Reply
Yumikang
Helper I
Helper I

How to tell if the vehicle has been inspected before entering the park？

‎07-25-2024 12:17 AM

Dear all,
 I recently had a problem that was very difficult for me.We urgently need your help.
I have three forms, which are Table 1 Vehicle entry Form, Table 2 Vehicle Inspection Form, and Table 3 Timetable.
My purpose is to create a new column in the vehicle entry form in Table 1 and name it Inspected Vehicles.Determine whether the vehicle has been inspected before entering ， and return the inspection time if it is inspected, or return the blank if it is not inspected.
Perform a line-by-line analysis of the vehicle entry form.
For example, in the first row of the vehicle entry form, first check whether the license plate number of the form is in the vehicle inspection form, and if it is not in the vehicle inspection form, it will return blank.
If the number are in the vehicle inspection form, compare the time of this line in the vehicle entry form with the time  in the vehicle inspection form, and find the first inspection time in the vehicle inspection form that is earlier than && closest to the time in the vehicle entry form. Otherwise, it returns blank.
Here is my form：
Table 1 Vehicle entry Form

license plate numberentry time
粤AGR8532024-05-23 12:30:15
皖A7Q0662024-05-23 12:29:41
粤AAN2032024-05-23 15:49:11
皖SH79982024-05-28 07:38:58
粤AM11252024-05-29 11:52:21
粤AAU9182024-05-29 11:45:20
粤WN20032024-05-29 12:49:57
粤AKP6662024-05-31 13:01:33
粤AAU9182024-06-03 14:01:21
皖A7M8882024-06-04 11:23:58
皖A7M8882024-06-04 13:00:17
粤AFA7712024-06-05 08:56:35
皖KV78052024-06-05 11:54:37
粤AAL9932024-06-05 13:53:00
粤AGM0382024-06-05 13:58:11
粤AFA7712024-06-05 15:34:37
粤AFA7712024-06-14 09:58:18
粤A85H9F2024-06-14 11:23:49
皖AD21132024-06-15 09:57:48
皖AD21132024-06-15 08:50:02
粤ABK2762024-06-15 13:18:03
粤AFA7712024-06-17 09:36:43
粤AFA7712024-06-17 15:49:38
粤AJN6552024-06-24 10:17:12
粤AAL0682024-05-09 19:13:06
鄂E364932024-06-03 09:16:24
豫D977382024-06-03 12:40:36
鄂E364932024-06-03 17:00:15
豫P977382024-06-03 19:31:22
鄂FL51052024-06-03 09:07:56
粤SC78962024-06-03 10:12:30
粤SE85692024-06-03 08:05:11
苏AN44272024-06-04 11:22:33
粤BHY1382024-06-03 20:00:59
粤ABE2982024-06-14 21:42:39
粤AAL0682024-06-04 15:06:44
粤AAL0682024-06-04 23:04:50
粤AAU5162024-06-04 16:44:31
鄂E364932024-06-05 08:56:48
粤BHY1382024-06-05 09:19:19
皖N330462024-06-14 10:42:38
鲁Q395JE2024-06-14 09:01:47
粤ABC3922024-06-14 11:34:42
粤BMP6522024-06-15 12:22:17
豫D977382024-06-15 16:15:23
粤BMP6522024-06-15 19:49:53
粤ABC3922024-06-15 10:48:53
粤SC78962024-06-15 19:48:08
鄂E364932024-06-16 10:49:19
鄂E364932024-06-16 20:52:37
粤SE85692024-06-16 09:55:04
鄂FF73802024-06-16 19:23:19
鄂FF73802024-06-16 19:11:29
粤BHY1382024-06-16 20:42:58
粤BMP6522024-06-17 07:46:34
豫P977382024-06-17 13:32:55
粤ABK2762024-06-24 14:48:52
皖A7S2992024-06-24 16:21:49
皖A7S2992024-06-24 14:49:05
粤AAL9932024-06-25 10:06:14
粤ABK2762024-06-25 10:08:28
皖A7J0992024-07-03 14:32:22
皖A7J0992024-07-03 14:32:32
粤AAU9182024-07-04 11:03:27
粤AA337772024-07-05 22:30:30
粤J25Q082024-07-15 10:37:10
皖A7N2992024-07-15 11:10:42
皖A7N2992024-07-15 11:10:29
粤WN20032024-07-19 11:32:07
粤WN20032024-07-19 11:17:15
鄂L571862024-07-19 11:42:15
鄂L571862024-07-19 11:42:26
粤ABK2762024-07-19 12:28:17
粤AFA7712024-07-19 14:50:15
粤ABK2762024-07-19 16:05:46
粤AJD1562024-07-20 08:55:08
粤ABK2762024-07-21 09:38:44
粤AAL9932024-07-22 09:33:19
粤AAU9182024-07-22 09:48:47
粤AFA7712024-07-22 10:48:23
粤AD94G12024-07-22 13:47:08
粤BMP6522024-06-17 17:07:15
粤SC78962024-06-17 09:26:43
粤SH55992024-06-17 10:43:59
粤SC78962024-06-17 17:38:36
皖N330462024-06-25 08:29:58
粤AAZ7072024-06-25 11:50:12
粤ABC3922024-06-24 08:38:24
豫P977382024-06-24 09:58:56
鲁Q395JE2024-06-24 12:37:29
粤BHY1382024-06-24 08:59:36
粤BMP6522024-06-25 11:00:35
鄂E364932024-06-25 16:14:05
鲁Q395JE2024-06-26 07:19:26
赣C3178E2024-06-25 08:42:51
粤BHY1382024-06-25 09:03:54
鄂FF73802024-06-26 06:03:55
赣C3178E2024-06-26 09:09:49
鄂FF73802024-06-26 06:03:44
鄂FF73802024-06-26 18:14:27
鄂FF73802024-06-26 18:14:18
沪D758182024-07-04 09:35:13
沪D758182024-07-04 16:42:50
皖N330462024-07-05 09:12:40
粤ABC3922024-07-04 14:51:18
沪D758182024-07-05 08:44:49
豫J763132024-07-05 13:24:51
赣C3882E2024-07-05 09:51:46
赣C3178E2024-07-05 08:48:04
鄂FLX7892024-07-05 16:47:33
鄂FLX7892024-07-05 16:47:25
粤ABC3922024-07-16 10:42:17
沪D758182024-07-19 07:34:53
沪ES27582024-07-19 08:35:54
沪D758182024-07-19 15:56:32
沪ES27582024-07-19 17:34:32
粤ABC3922024-07-19 10:53:47
粤ABC3922024-07-19 16:03:32
沪D758182024-07-20 11:04:45
皖N330462024-07-22 09:05:06
粤ABC3922024-07-20 13:50:37
沪D758182024-07-21 07:49:56
赣C3178E2024-07-21 09:30:58
赣C1303E2024-07-21 10:44:44
赣C1155E2024-07-21 10:05:45
粤SE85692024-07-21 11:27:12
沪ES27582024-07-21 10:48:57
沪ES27582024-07-21 20:56:14
沪D758182024-07-21 19:35:55
  
苏E212162024-07-21 13:07:05
Labels:
Message 1 of 9
286 Views
0
1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User
In response to Yumikang

‎07-26-2024 01:00 AM

hi @Yumikang ,

 

you can add additional conditions like:

Column1 =
VAR _entrytime = Table1[entry time]
VAR _result =
MAXX(
    FILTER(
        table2,
        Table2[license plate number]=Table1[license plate number]
            &&Table2[Inspection time]<=_entrytime
            &&Table2[Inspection time]>=_entrytime-7
    ),
    [Inspection time]
)
RETURN _result

View solution in original post

Message 7 of 9
190 Views
0
8 REPLIES 8
Yumikang
Helper I
Helper I

‎07-25-2024 12:19 AM

Table 2: Vehicle inspection form

 

Inspection time inspection datelicense plate number
2024/6/6 14:262024/6/6赣CBM197
2024/6/6 14:302024/6/6粤AS3119
2024/6/6 14:322024/6/6粤A3E96Q
2024/6/6 14:342024/6/6粤A5V11T
2024/6/6 19:302024/6/6粤SC7896
2024/6/7 9:472024/6/7粤AAU516
2024/6/7 9:542024/6/7粤AAY123
2024/6/7 9:572024/6/7鄂FL5105
2024/6/7 15:462024/6/7粤WN2003
2024/6/7 15:512024/6/7皖N33046
2024/6/7 16:002024/6/7粤A38VN6
2024/6/11 12:022024/6/11粤BHY138
2024/6/12 10:132024/6/12桂P50183
2024/6/14 13:212024/6/14粤ABK276
2024/6/14 16:012024/6/13鲁Q395JE
2024/6/16 10:282024/6/16鄂FF7380
2024/6/16 14:012024/6/16粤SE8569
2024/6/16 14:032024/6/16粤SE7753
2024/6/24 9:122024/6/23豫D97738
2024/6/24 17:082024/6/24粤AAL951
2024/6/24 17:082024/6/24粤AAL993
2024/6/27 14:262024/6/29豫J76313
2024/6/27 14:262024/6/29豫JY773
2024/7/8 11:062024/7/8豫J76313
2024/7/16 19:422024/7/16粤AAS759
2024/7/18 14:432024/7/189.6飞翼
2024/7/19 8:502024/7/19粤AAN203
2024/7/19 9:012024/7/19鄂L57186
2024/7/19 15:532024/7/19粤AJD156
2024/7/20 14:172024/7/20赣C1155E
2024/7/20 14:192024/7/20

赣C3882E

Table 3: time form

date
202401
202402
202403
202404
202405
202406
202407
202408
202409
202410
202411
202412

The relationship I'm trying to model is this

Yumikang_0-1721891978619.png

 

 

Message 2 of 9
283 Views
0
FreemanZ
Super User
Super User
In response to Yumikang

‎07-25-2024 12:29 AM

hi @Yumikang , 

 

In table1, try to add a calculated column like:

 

column =
MAXX(
    FILTER(
        table2,
        Table2[license plate number]=Table1[license plate number]
            &&Table2[Inspection time]<=Table1[entry time]
    ),
    [Inspection time]
)

 

 

it is also adivisble to do it in Power Query.

Message 3 of 9
265 Views
0
Yumikang
Helper I
Helper I
In response to FreemanZ

‎07-25-2024 12:54 AM

Thanks for your reply.I just tested it.

However, my requirement is that the vehicle needs to be inspected before entrying the park, otherwise it will be blank when the new column is new.However, according to the DAX function, it is also found that the inspection time is later than the admission time, such as the red box in the figure below.

I would like to the new column of time is return to  earlier than the time of entry(form1 vehicle entry form-entry time), and it is the closest to the time of entry(form1 vehicle entry form-entry time).

Yumikang_1-1721894072065.png

 

 

Message 4 of 9
256 Views
0
FreemanZ
Super User
Super User
In response to Yumikang

‎07-25-2024 01:19 AM

hi @Yumikang ,

 

i have updated the code in the post as:


column =
MAXX(
FILTER(
table2,
Table2[license plate number]=Table1[license plate number]
&&Table2[Inspection time]<=Table1[entry time]
),
[Inspection time]
)

 

please try again

Message 5 of 9
253 Views
Yumikang
Helper I
Helper I
In response to FreemanZ

‎07-25-2024 03:07 AM

Below is  screenshot of my latest vehicle entry form.

Yumikang_1-1721901757331.png

I found that my measure 2 should be wrong because in the form the car is only checked 1 time, but in my measure 2 it shows 6 times.

And the similar time of entering the park should also be regarded as only one entry, not 6 times.

Yumikang_2-1721902034262.png

 

Message 9 of 9
216 Views
0
Yumikang
Helper I
Helper I
In response to FreemanZ

‎07-25-2024 02:42 AM

thank you for your reply.

I would like to know if the  recent week before entrying the park has been inspected, so I have added a new column: it is calculated with the datediff function, and the datediff returns the type of week.

datediff = DATEDIFF([check time],[entry time],WEEK)
A new column has been added to determine if it is within a week.
datediff2 = if([datediff]<=1&&isblank([check time])=false,1,blank())
A new measure is created to calculate the total number of vehicle inspections.
measure1 = sum('Vehicle entry form'[datediff2])
So I got the matrix below
Yumikang_0-1721900455000.png

Do you have an easier way? I think mine is too cumbersome because I have a lot of new columns.

Message 6 of 9
220 Views
0
FreemanZ
Super User
Super User
In response to Yumikang

‎07-26-2024 01:00 AM

hi @Yumikang ,

 

you can add additional conditions like:

Column1 =
VAR _entrytime = Table1[entry time]
VAR _result =
MAXX(
    FILTER(
        table2,
        Table2[license plate number]=Table1[license plate number]
            &&Table2[Inspection time]<=_entrytime
            &&Table2[Inspection time]>=_entrytime-7
    ),
    [Inspection time]
)
RETURN _result
Message 7 of 9
191 Views
0
Yumikang
Helper I
Helper I
In response to FreemanZ

‎07-26-2024 01:45 AM

thank you very much 

Message 8 of 9
186 Views
0

Helpful resources

Announcements
Europe Fabric Conference

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.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

View All