Create a list of trucks not on orders compared to list by date slicer

[DaGemsta]

Hi guys

I'm completely stuck on this. 

I have a really strange table created by the connector we're using to pull data from NetSuite into SQL. 

I've mocked up a sample of the table here

The first 14 lines are orders for the 12th Jan (filtered by the date slicer on the page). Some trucks have more than 1 order against them for that day. 

The Fleet Name.2 column is a master list of all the trucks we have in the company. It's not related to any orders. 

I need to be able to produce a list of all the Trucks that did not have an order against them for this date (or any date the date slicer is set to)

Any ideas? Thanks in advance! 

[mahoneypat]

You can just make one query using just the FleetName.2 column (remove other columns and remove blank rows) to make a Trucks table, and a 2nd with your other columns (also remove blanks) called Locations (or whatever you want). You can then add a Date table, and relate the Trucks and Date tables to the Locations table (1:Many) on the matching columns.

In your visuals, you can just add "+ 0" to your measures to see Trucks with no data on those days.

Pat

[littlemojopuppy]

Listen to @mahoneypat.  He's forgotten more about Power BI than most of us ever knew 🙂

[mahoneypat]

Too kind @littlemojopuppy . While I have been studying M and DAX for almost 10 yrs, I still aspire to be as good as Marco/Alberto in DAX or Chris/Imke in M.

Pat

[DaGemsta]

Hi Pat

thanks so much for your reply. 

I have no idea how to do any of that lol. 

Would you be able to give me a step by step please?

[mahoneypat]

Ok. Please supply your sample data (not as an image). You can insert a table in your reply and paste it there, or provide a link to your data as csv or xlsx on Google Drive, OneDrive, other.  I (or someone else in the community can send you back a pbix file as an example.

Pat

[DaGemsta]

Thanks Pat

I remembered I also need to exclude any trucks with a retirement date so I've added that as well

https://neweraoil-my.sharepoint.com/:x:/g/personal/admin_newerafuels_co_uk/EZvsk-Mmw4NKr3l5nEXc9FwB8...

[mahoneypat · Accepted solution]

Attached is a start on your pbix file. It includes the following:

• Trucks query - filters out the retired truck, and removes the delivery info columns
• Deliveries query - remove the Trucks columns
• Added a Date table that will auto expand to match the range of Delivery Dates in the Deliveries table. Also "Marked it as Date Table".
• Made relationships between Date and Trucks table to the Deliveries table.
• Added a Measures table (optional, but good practice)
• Created a simple measure to count the # of docs delivered + 0 (so you will see a result for all trucks, even if no deliveries
• Created a simple matrix to show that measure for each Truck and day

You will need to update the Source line in each query with the path to your local file. Note that it is better to store your files on SharePoint or OneDrive (so you can easily set up scheduled refresh and collaborate with others), but local file is OK too.

Pat

[DaGemsta]

Amazing!

Thanks SO much Pat. I've manged to achieve what I needed to using your example and tweaking it to match my SQL source.

I bow to your PBI power! 😄