Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DaGemsta
Helper I
Helper I

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.