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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply

Using non related calendar table to get distinct count from two tables

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_IdVessel_NameVessel_FleetVessel_TypeMaker_NameUltimate_OwnerPrimary_ManagerPrimary_Manager_Valid_FromPrimary_Manager_Valid_To
304782Vessel 1DryContainer< MAN B&W >Owner 1Company 19/26/2017NULL
304781Vessel 2TankerOil TankerBurmeister & WainOwner 2Company 29/27/2017NULL
304780Vessel 3GasGas TankerBurmeister & WainOwner 3Company 39/28/2017NULL
304779Vessel 4DryBulk Carrier< MAN B&W >Owner 4Company 29/29/2017NULL
304778Vessel 5DryContainerBurmeister & WainOwner 5Company 29/29/2017NULL
304777Vessel 6DryBulk Carrier< MAN B&W >Owner 6Company 29/29/2017NULL
304776Vessel 7DryBulk CarrierMAN B&WOwner 7Company 610/31/2017NULL
304775Vessel 8DryContainer< SULZER >Owner 8Company 211/1/2017NULL
304774Vessel 9DryBulk CarrierOtherOwner 9Company 211/2/2017NULL
304773Vessel 10DryBulk CarrierMAN B&WOwner 10Company 211/3/2017NULL
304772Vessel 11DryContainer< MAN B&W >Owner 11Company 211/4/2017NULL
304771Vessel 12DryContainer< MAN B&W >Owner 12Company 211/5/2017NULL
304770Vessel 13DryContainerBurmeister & WainOwner 13Company 211/6/2017NULL
304769Vessel 14DryContainer-MITSUBISHI-Owner 14Company 29/29/2017NULL
304768Vessel 15DryContainer< MAN B&W >Owner 15Company 29/29/2017NULL
304767Vessel 16DryContainer< SULZER >Owner 1Company 29/29/2017NULL
304766Vessel 17DryContainer< SULZER >Owner 2Company 29/29/2017NULL
304765Vessel 18DryContainer< MAN B&W >Owner 3Company 29/29/2017NULL
304764Vessel 19DryContainer< MAN B&W >Owner 4Company 29/29/2017NULL
304763Vessel 20DryContainerBurmeister & WainOwner 5Company 29/29/2017NULL
304762Vessel 21DryContainer< MAN B&W >Owner 6Company 29/29/2017NULL
304761Vessel 22DryContainer< MAN B&W >Owner 7Company 29/29/2017NULL
304760Vessel 23DryBulk CarrierBurmeister & WainOwner 8Company 29/29/2017NULL
304759Vessel 24TankerOil TankerBurmeister & WainOwner 9Company 410/26/2017NULL
304758Vessel 25OffshoreOffshoreOtherOwner 10Company 210/27/2017NULL
304757Vessel 26DryBulk CarrierMAN B&WOwner 11Company 510/28/2017NULL
304756Vessel 27OffshoreOffshoreOtherOwner 12Company 210/22/2017NULL
304755Vessel 28TankerOil Tanker< MAN B&W >Owner 13Company 110/23/2017NULL
304754Vessel 29TankerOil TankerSTXOwner 14Company 610/24/2017NULL
304753Vessel 30GasGas Tanker-WARTSILA-Owner 15Company 310/25/2017NULL

 

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 IDDONE DATEVessel IDPLANNED JOB IDUNPLANNED JOB IDDUE DATEJOB TITLE
10010000907/7/201630478299990109793999901104519/12/2016RENEW WIRE ROPES
10010000927/7/2016304781999901097959999011045311/20/2016RENEW WIRE ROPES
10010000937/8/201630478099990109800999901104589/12/2016RENEW WIRE ROPES
10010000947/7/2016304758999901097969999011045411/20/2016RENEW WIRE ROPES
10010003397/31/201630475799990109232999901098908/3/2016I523 - INSPECT PURIFIER BRAKE
10010008411/28/201730475699990109319999901099771/28/2017FULL OVERHAUL ELECTRIC MOTOR.
10010008424/30/201630475599990109315999901099738/13/2016FULL OVERHAUL ELECTRIC MOTOR.
10010008434/30/2016304754999901092749999010993212/30/2016FULL OVERHAUL ELECTRIC MOTOR.
10010008448/31/201730475399990109092999901097508/12/2017FULL OVERHAUL ELECTRIC MOTOR.
100100084512/8/2016304782999901093909999011004812/30/2016FULL OVERHAUL ELECTRIC MOTOR.
100100084612/5/2016304781999901093929999011005012/30/2016FULL OVERHAUL ELECTRIC MOTOR.
100100084912/2/2016304780 9999011004412/30/2016FULL OVERHAUL ELECTRIC MOTOR.
10010008505/31/2016304758 999901099247/28/2016FULL OVERHAUL ELECTRIC MOTOR.
100100085212/27/2015304757 999901100424/27/2016FULL OVERHAUL ELECTRIC MOTOR.
10010008532/29/201630475699990109323999901099812/27/2016FULL OVERHAUL ELECTRIC MOTOR.
10010009864/30/201630475599990109307999901099656/26/2016I530 - INSPECT-PURIFIER + BALL BEARINGS
10010010171/1/201630475499990109517999901101751/1/2016GMDSS SHORE-BASED MAINTENANCE
100100126712/1/201530475399990109803999901104611/10/2016CHANGE OIL IN TURNING DEVICE
10010013346/30/2016304782999901091049999010976210/23/2016OVERHAUL PUMP
100100133510/19/2016304781999901093859999011004312/17/2016OVERHAUL PUMP
10010013364/16/2017304780 9999010992911/5/2017OVERHAUL PUMP
100100137010/18/2016304758 9999011004712/16/2016OVERHAUL PUMP
10010014481/7/2016304757 999901105131/28/2016TANK CLEANING.
10010016322/5/2016304756 999901120492/5/2016OIL MIST ANNUAL MAINTENANCE
10010018331/1/2016304755 999901101611/1/2016FLAG STATE ANNUAL TAXES
10010021522/26/201730475499990109387999901100452/26/2017OVERHAUL PUMP
10010022021/18/201630475399990109693999901103511/18/2016ANNUAL MACHINERY SURVEY
10010024182/5/201630478299990110086999901107442/28/2016BLOWER CONDITION CHECK - ANNUALLY
10010024717/27/201530478199990109477999901101357/5/2016L019 - 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

 

 

 

 

 

 

 

 

 

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@bikram_laishram,

 

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.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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