Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hello Folks,
Merry Xmas and Happy new year in advance.
I am looking to build a report which will help me evaluate all breakdowns in a date range based on factors like builders, owner or primary managers. I have two tables one for holding the fleet information having a start date and and date. The other tables has work orders related information like equipment info and the last done date. the sample files are below for your perusal
Vessel_Id | Vessel_Name | Vessel_Fleet | Vessel_Type | Maker_Name | Ultimate_Owner | Primary_Manager | Primary_Manager_Valid_From | Primary_Manager_Valid_To |
304782 | Vessel 1 | Dry | Container | < MAN B&W > | Owner 1 | Company 1 | 9/26/2017 | NULL |
304781 | Vessel 2 | Tanker | Oil Tanker | Burmeister & Wain | Owner 2 | Company 2 | 9/27/2017 | NULL |
304780 | Vessel 3 | Gas | Gas Tanker | Burmeister & Wain | Owner 3 | Company 3 | 9/28/2017 | NULL |
304779 | Vessel 4 | Dry | Bulk Carrier | < MAN B&W > | Owner 4 | Company 2 | 9/29/2017 | NULL |
304778 | Vessel 5 | Dry | Container | Burmeister & Wain | Owner 5 | Company 2 | 9/29/2017 | NULL |
304777 | Vessel 6 | Dry | Bulk Carrier | < MAN B&W > | Owner 6 | Company 2 | 9/29/2017 | NULL |
304776 | Vessel 7 | Dry | Bulk Carrier | MAN B&W | Owner 7 | Company 6 | 10/31/2017 | NULL |
304775 | Vessel 8 | Dry | Container | < SULZER > | Owner 8 | Company 2 | 11/1/2017 | NULL |
304774 | Vessel 9 | Dry | Bulk Carrier | Other | Owner 9 | Company 2 | 11/2/2017 | NULL |
304773 | Vessel 10 | Dry | Bulk Carrier | MAN B&W | Owner 10 | Company 2 | 11/3/2017 | NULL |
304772 | Vessel 11 | Dry | Container | < MAN B&W > | Owner 11 | Company 2 | 11/4/2017 | NULL |
304771 | Vessel 12 | Dry | Container | < MAN B&W > | Owner 12 | Company 2 | 11/5/2017 | NULL |
304770 | Vessel 13 | Dry | Container | Burmeister & Wain | Owner 13 | Company 2 | 11/6/2017 | NULL |
304769 | Vessel 14 | Dry | Container | -MITSUBISHI- | Owner 14 | Company 2 | 9/29/2017 | NULL |
304768 | Vessel 15 | Dry | Container | < MAN B&W > | Owner 15 | Company 2 | 9/29/2017 | NULL |
304767 | Vessel 16 | Dry | Container | < SULZER > | Owner 1 | Company 2 | 9/29/2017 | NULL |
304766 | Vessel 17 | Dry | Container | < SULZER > | Owner 2 | Company 2 | 9/29/2017 | NULL |
304765 | Vessel 18 | Dry | Container | < MAN B&W > | Owner 3 | Company 2 | 9/29/2017 | NULL |
304764 | Vessel 19 | Dry | Container | < MAN B&W > | Owner 4 | Company 2 | 9/29/2017 | NULL |
304763 | Vessel 20 | Dry | Container | Burmeister & Wain | Owner 5 | Company 2 | 9/29/2017 | NULL |
304762 | Vessel 21 | Dry | Container | < MAN B&W > | Owner 6 | Company 2 | 9/29/2017 | NULL |
304761 | Vessel 22 | Dry | Container | < MAN B&W > | Owner 7 | Company 2 | 9/29/2017 | NULL |
304760 | Vessel 23 | Dry | Bulk Carrier | Burmeister & Wain | Owner 8 | Company 2 | 9/29/2017 | NULL |
304759 | Vessel 24 | Tanker | Oil Tanker | Burmeister & Wain | Owner 9 | Company 4 | 10/26/2017 | NULL |
304758 | Vessel 25 | Offshore | Offshore | Other | Owner 10 | Company 2 | 10/27/2017 | NULL |
304757 | Vessel 26 | Dry | Bulk Carrier | MAN B&W | Owner 11 | Company 5 | 10/28/2017 | NULL |
304756 | Vessel 27 | Offshore | Offshore | Other | Owner 12 | Company 2 | 10/22/2017 | NULL |
304755 | Vessel 28 | Tanker | Oil Tanker | < MAN B&W > | Owner 13 | Company 1 | 10/23/2017 | NULL |
304754 | Vessel 29 | Tanker | Oil Tanker | STX | Owner 14 | Company 6 | 10/24/2017 | NULL |
304753 | Vessel 30 | Gas | Gas Tanker | -WARTSILA- | Owner 15 | Company 3 | 10/25/2017 | NULL |
The first requirement was to get a list of active vessels between a user defined date range using two columns from this table i.e. 'Primary Manager Valid from' & 'Primary Manager Valid to'.
@v-jiascu-msft help me with the DAX required for the result which was as follows:
Vessel Count = CALCULATE(DISTINCTCOUNT('Vessel Register'[ID]), filter('Vessel Register', 'Vessel Register'[PM Start Date]<=min('Calendar'[Date])&&'Vessel Register'[PM End Date]>=max('Calendar'[Date])))
where i used the following query to build the calendar table "Calendar = CALENDARAUTO()" also recommended by @v-jiascu-msft
Now i have a second table,
Job ID | DONE DATE | Vessel ID | PLANNED JOB ID | UNPLANNED JOB ID | DUE DATE | JOB TITLE |
1001000090 | 7/7/2016 | 304782 | 99990109793 | 99990110451 | 9/12/2016 | RENEW WIRE ROPES |
1001000092 | 7/7/2016 | 304781 | 99990109795 | 99990110453 | 11/20/2016 | RENEW WIRE ROPES |
1001000093 | 7/8/2016 | 304780 | 99990109800 | 99990110458 | 9/12/2016 | RENEW WIRE ROPES |
1001000094 | 7/7/2016 | 304758 | 99990109796 | 99990110454 | 11/20/2016 | RENEW WIRE ROPES |
1001000339 | 7/31/2016 | 304757 | 99990109232 | 99990109890 | 8/3/2016 | I523 - INSPECT PURIFIER BRAKE |
1001000841 | 1/28/2017 | 304756 | 99990109319 | 99990109977 | 1/28/2017 | FULL OVERHAUL ELECTRIC MOTOR. |
1001000842 | 4/30/2016 | 304755 | 99990109315 | 99990109973 | 8/13/2016 | FULL OVERHAUL ELECTRIC MOTOR. |
1001000843 | 4/30/2016 | 304754 | 99990109274 | 99990109932 | 12/30/2016 | FULL OVERHAUL ELECTRIC MOTOR. |
1001000844 | 8/31/2017 | 304753 | 99990109092 | 99990109750 | 8/12/2017 | FULL OVERHAUL ELECTRIC MOTOR. |
1001000845 | 12/8/2016 | 304782 | 99990109390 | 99990110048 | 12/30/2016 | FULL OVERHAUL ELECTRIC MOTOR. |
1001000846 | 12/5/2016 | 304781 | 99990109392 | 99990110050 | 12/30/2016 | FULL OVERHAUL ELECTRIC MOTOR. |
1001000849 | 12/2/2016 | 304780 | 99990110044 | 12/30/2016 | FULL OVERHAUL ELECTRIC MOTOR. | |
1001000850 | 5/31/2016 | 304758 | 99990109924 | 7/28/2016 | FULL OVERHAUL ELECTRIC MOTOR. | |
1001000852 | 12/27/2015 | 304757 | 99990110042 | 4/27/2016 | FULL OVERHAUL ELECTRIC MOTOR. | |
1001000853 | 2/29/2016 | 304756 | 99990109323 | 99990109981 | 2/27/2016 | FULL OVERHAUL ELECTRIC MOTOR. |
1001000986 | 4/30/2016 | 304755 | 99990109307 | 99990109965 | 6/26/2016 | I530 - INSPECT-PURIFIER + BALL BEARINGS |
1001001017 | 1/1/2016 | 304754 | 99990109517 | 99990110175 | 1/1/2016 | GMDSS SHORE-BASED MAINTENANCE |
1001001267 | 12/1/2015 | 304753 | 99990109803 | 99990110461 | 1/10/2016 | CHANGE OIL IN TURNING DEVICE |
1001001334 | 6/30/2016 | 304782 | 99990109104 | 99990109762 | 10/23/2016 | OVERHAUL PUMP |
1001001335 | 10/19/2016 | 304781 | 99990109385 | 99990110043 | 12/17/2016 | OVERHAUL PUMP |
1001001336 | 4/16/2017 | 304780 | 99990109929 | 11/5/2017 | OVERHAUL PUMP | |
1001001370 | 10/18/2016 | 304758 | 99990110047 | 12/16/2016 | OVERHAUL PUMP | |
1001001448 | 1/7/2016 | 304757 | 99990110513 | 1/28/2016 | TANK CLEANING. | |
1001001632 | 2/5/2016 | 304756 | 99990112049 | 2/5/2016 | OIL MIST ANNUAL MAINTENANCE | |
1001001833 | 1/1/2016 | 304755 | 99990110161 | 1/1/2016 | FLAG STATE ANNUAL TAXES | |
1001002152 | 2/26/2017 | 304754 | 99990109387 | 99990110045 | 2/26/2017 | OVERHAUL PUMP |
1001002202 | 1/18/2016 | 304753 | 99990109693 | 99990110351 | 1/18/2016 | ANNUAL MACHINERY SURVEY |
1001002418 | 2/5/2016 | 304782 | 99990110086 | 99990110744 | 2/28/2016 | BLOWER CONDITION CHECK - ANNUALLY |
1001002471 | 7/27/2015 | 304781 | 99990109477 | 99990110135 | 7/5/2016 | L019 - CHANGE OIL & CLEAN / REPLACE FILTER |
Is it possible to prepare a dax which will give me a distinct count of all unplanned job ID based on the same max and minimum date filter I applied on the dax above. The date filter needs to be applied on the "done date' column. So when I select the vessel count dax, it should immidiately show me disctinct count of unplanned job ids.
Any help on this matter will be greatly appreaciated.
Best regards,
Bikram
It seems that you may just replace the column name in formula above. To get a more accurate answer, simplify the example and show us the expected output.
User | Count |
---|---|
84 | |
78 | |
71 | |
48 | |
42 |
User | Count |
---|---|
111 | |
56 | |
50 | |
41 | |
40 |