Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
The problem -
Our sales team gets company-provided vehicles. Each month, we get an excel file that has information on the number miles of being driven by various employees in these leased vehicles.
A snippet of the merged files -
Driver Employee ID | Days Reported | Odometer | Personal Miles | Business Miles | Total Miles | Date |
1111 | 31 | 79397 | 108 | 579 | 687 | 12/1/2019 |
2222 | 31 | 72987 | 144 | 878 | 1022 | 1/1/2020 |
1111 | 30 | 91909 | 350 | 414 | 764 | 11/1/2019 |
4444 | 0 | 0 | 0 | 0 | 2/1/2020 | |
5555 | 31 | 61025 | 297 | 975 | 1272 | 10/1/2019 |
I have to create a report that calculates –
Basically, we need to check if it is worth giving employees leased vehicles. If they are projected to completed 12,000 miles in a year, their [Compliance Status] is “Compliant”. Otherwise they are “Not-Compliant”.
This table is called ‘Fleet’.
The issue is that there is another table – ‘Exception Drivers’ - that has a list of drivers who are not to be considered for the above mentioned compliance.
Employee ID | Exception Type | Start Date | Expiration Date |
1111 | 1 | 1/1/2018 | 1/1/2021 |
2222 | 2 | 1/1/2019 | 7/1/2020 |
We are provided with a [Start Date] and an [End Date]. Anytime a driver falls within this date range, he/she should be considered as an "Exception". So now, my calc. column – [Compliance Status] - will have 3 values, i.e., "Compliant", "Non-Compliant" and "Exception".
Thing to note – Fleet table and Exception Drivers table are linked by the Driver/Emp ID.
The mapping from ‘Fleet’ to ‘Exception Drivers’ is *:1 (Many-to-1)
My Question –
How do I look up the ‘Exception Driver’ table and return the class of “Exception” into the column [Compliance Status] which is part of the ‘Fleet’ Table. Also, I must keep in mind the [Start Date] and [End Date] in the ‘Exception Driver’ table.
Truly appreciate your patience and comments.
PS: I made changes after @Greg_Deckler suggested I make it simpler for users to understand the problem. Tried to include a a few rows of data and the measures as well. Hope this helps.
Well, if the data range doesn't matter:
VAR __Exempt = DISTINCT('Table'[Employee ID])
IF(MAX([PRSN_ID])) IN __Exempt,"Excempt", <calculation goes here>
If dates do matter, then I feel there is missing information that would be required to complete a suitable answer. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
@Greg_Deckler Thanks for the speedy response! I've updated my question. Is there anyway you can share with me what happens if we have to consider the date range as well?
Best Regards