Hi all,
I've watched a lot of videos and functions on my own, but none of them solved my problem, so I beg you to help me. I now have two forms，One is the vehicle entry form and the other is the vehicle inspection form.There is a date table between them that serves as a bridge to connect.The date table is 1 port, and the vehicle inspection table and vehicle admission table are multi-port.
1. I would like to find out if the vehicle has been inspected at this month of year.
2. And to find out which vehicles were inspected before they entered the park.
I would like to compare the vehicles in the vehicle entry form with the vehicle inspection list to see if the vehicles entering the park have been inspected before entering the park.
I would like to create a new column in the vehicle entry table named "Inspected Vehicles1"
Analyze the vehicle inspection form and the vehicle entry form line by line, and first compare whether the entry form the column of" year and month" in the entry form are consistent with the column of "year and month" in the inspection form; If it is consistent, compare whether the license plate numbers in the two tables are the same, and return the license plate number if they are the same; If the time of "year and month" are inconsistent, the system is empty.
I would like to find out if the vehicle has been inspected at this month of year.
I would like to create a new column in the vehicle entry table named "Inspected Vehicles2"
Perform a line-by-line analysis of the vehicle entry form and the vehicle inspection form, first compare whether the entry time in the emtry form is earlier than the data in each row of the inspection table, and if so, return empty. If not, compare them line by line, and match the license plate number in the park entry checklist line by line in the inspection form.
My goal was to find out which vehicles were inspected before they entered the park.
please help me,much appreciated.
Hi, @Yumikang
You can try the following dax to achieve your need.
DAX:
currentMonthisIspection =
IF (
NOT (
ISBLANK (
LOOKUPVALUE (
'Vechicle inspection form'[license plate number],
'Vechicle inspection form'[license plate number], 'Vehicle entry form'[license plate number],
'Vechicle inspection form'[YearMonth], 'Vehicle entry form'[YearMonth]
)
)
),
'Vehicle entry form'[license plate number],
"Null"
)
Checked prior to entry =
VAR _entryDate = 'Vehicle entry form'[entry time]
VAR _plateNumber = 'Vehicle entry form'[license plate number]
VAR _year =
YEAR ( 'Vehicle entry form'[entry time] )
VAR _month =
MONTH ( 'Vehicle entry form'[entry time] )
VAR _day =
DAY ( 'Vehicle entry form'[entry time] )
VAR _inspectionDate =
LOOKUPVALUE (
'Vechicle inspection form'[inspection date],
'Vechicle inspection form'[license plate number], _plateNumber,
'Vechicle inspection form'[inspection date],
CALCULATE (
MIN ( 'Vechicle inspection form'[inspection date] ),
FILTER (
'Vechicle inspection form',
'Vechicle inspection form'[license plate number] = _plateNumber
&& YEAR ( 'Vechicle inspection form'[inspection date] ) = _year
&& MONTH ( 'Vechicle inspection form'[inspection date] ) = _month
&& DAY ( 'Vechicle inspection form'[inspection date] ) > _day
)
)
)
RETURN
IF ( NOT ( ISBLANK ( _inspectionDate ) ), _plateNumber, "NUll" )
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi, @Yumikang
You can try the following dax to achieve your need.
DAX:
currentMonthisIspection =
IF (
NOT (
ISBLANK (
LOOKUPVALUE (
'Vechicle inspection form'[license plate number],
'Vechicle inspection form'[license plate number], 'Vehicle entry form'[license plate number],
'Vechicle inspection form'[YearMonth], 'Vehicle entry form'[YearMonth]
)
)
),
'Vehicle entry form'[license plate number],
"Null"
)
Checked prior to entry =
VAR _entryDate = 'Vehicle entry form'[entry time]
VAR _plateNumber = 'Vehicle entry form'[license plate number]
VAR _year =
YEAR ( 'Vehicle entry form'[entry time] )
VAR _month =
MONTH ( 'Vehicle entry form'[entry time] )
VAR _day =
DAY ( 'Vehicle entry form'[entry time] )
VAR _inspectionDate =
LOOKUPVALUE (
'Vechicle inspection form'[inspection date],
'Vechicle inspection form'[license plate number], _plateNumber,
'Vechicle inspection form'[inspection date],
CALCULATE (
MIN ( 'Vechicle inspection form'[inspection date] ),
FILTER (
'Vechicle inspection form',
'Vechicle inspection form'[license plate number] = _plateNumber
&& YEAR ( 'Vechicle inspection form'[inspection date] ) = _year
&& MONTH ( 'Vechicle inspection form'[inspection date] ) = _month
&& DAY ( 'Vechicle inspection form'[inspection date] ) > _day
)
)
)
RETURN
IF ( NOT ( ISBLANK ( _inspectionDate ) ), _plateNumber, "NUll" )
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Thanks for the reply, I've got the test file ready, but how do I send the attachment? I didn't find the option to add an attachment.
Hi, @Yumikang
Sorry, the links you shared doesn't open. I'm not a user in your organization.
If the amount of data used for the test is small, you can just copy your table, same as the posters below.
Solved: Return if one condition OR another is met - Microsoft Fabric Community
If you have a large amount of data, you can use GoogleDrive or OneDrive to share your links.
Solved: RANKX Measure failing to return correct results af... - Microsoft Fabric Community
Best Regards,
Yang
Community Support Team
Table 2: Vehicle inspection form
| Inspection time | inspection date | license plate number |
| 2024/6/6 14:26 | 2024/6/6 | 赣CBM197 |
| 2024/6/6 14:30 | 2024/6/6 | 粤AS3119 |
| 2024/6/6 14:32 | 2024/6/6 | 粤A3E96Q |
| 2024/6/6 14:34 | 2024/6/6 | 粤A5V11T |
| 2024/6/6 19:30 | 2024/6/6 | 粤SC7896 |
| 2024/6/7 9:47 | 2024/6/7 | 粤AAU516 |
| 2024/6/7 9:54 | 2024/6/7 | 粤AAY123 |
| 2024/6/7 9:57 | 2024/6/7 | 鄂FL5105 |
| 2024/6/7 15:46 | 2024/6/7 | 粤WN2003 |
| 2024/6/7 15:51 | 2024/6/7 | 皖N33046 |
| 2024/6/7 16:00 | 2024/6/7 | 粤A38VN6 |
| 2024/6/11 12:02 | 2024/6/11 | 粤BHY138 |
| 2024/6/12 10:13 | 2024/6/12 | 桂P50183 |
| 2024/6/14 13:21 | 2024/6/14 | 粤ABK276 |
| 2024/6/14 16:01 | 2024/6/13 | 鲁Q395JE |
| 2024/6/16 10:28 | 2024/6/16 | 鄂FF7380 |
| 2024/6/16 14:01 | 2024/6/16 | 粤SE8569 |
| 2024/6/16 14:03 | 2024/6/16 | 粤SE7753 |
| 2024/6/24 9:12 | 2024/6/23 | 豫D97738 |
| 2024/6/24 17:08 | 2024/6/24 | 粤AAL951 |
| 2024/6/24 17:08 | 2024/6/24 | 粤AAL993 |
| 2024/6/27 14:26 | 2024/6/29 | 豫J76313 |
| 2024/6/27 14:26 | 2024/6/29 | 豫JY773 |
| 2024/7/8 11:06 | 2024/7/8 | 豫J76313 |
| 2024/7/16 19:42 | 2024/7/16 | 粤AAS759 |
| 2024/7/18 14:43 | 2024/7/18 | 9.6飞翼 |
| 2024/7/19 8:50 | 2024/7/19 | 粤AAN203 |
| 2024/7/19 9:01 | 2024/7/19 | 鄂L57186 |
| 2024/7/19 15:53 | 2024/7/19 | 粤AJD156 |
| 2024/7/20 14:17 | 2024/7/20 | 赣C1155E |
| 2024/7/20 14:19 | 2024/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
I want to create 2 new columns in the vehicle entry table
1.Compare if this vehicle was inspected in a certain month. For example, if the entry time is 202406 in entry form, then look for 202406 in the vehicle inspection form to see if there is a license plate number for this vehicle.
If there is, return this license plate number, if not, return empty.
2.In a given month, compare whether the car was inspected before it was admitted to entry the park. For example, if the entry time of “粤 AA036” in the Vehicle Entry Table is 2024/06/23, then see if there is this license plate number in the Vehicle Inspection form between 2024/06/01 and 2024/06/22. If there is, return this license plate number, if not, return empty.
3.Table 3 shows my slicer, with which I filtered the formulated months.
Helpful resources
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
