cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DaGemsta
Frequent Visitor

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

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

 

DaGemsta_0-1643402888486.jpeg

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! 

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

 

mahoneypat_0-1643462133700.png

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

8 REPLIES 8
mahoneypat
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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?

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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.

 

mahoneypat_0-1643462133700.png

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors