Hi all! I am brand new on Power BI and I have been puzzling with the following.
I have three data sources in Power BI.
1) Table (Typerating) with aircraftregistration (PH-AAA and PH-BBB etc) and related typerating (Cessna Citation, Mustang, Falcon etc)- 2 columns in this datatable with all aircrafts and all typeratings
2) Table with historic flights (Flights) so far specified by aircraft registration and a crew string in which all involved pilots are included with their unique code (3 letter code each pilot) - There are many columns in this datatable. Relevant colums here are crew string ( e.g. : AAA BBB) , off block time (00:30) and aircraft registration (PH-AAA). In each row a flight mission is included with all involved crew, aircraft, time flown etc. This is the datadump of our flight registration application with all historic data.
3) Table with the names of all pilots (Pilots) that have been employed so far (one column with a list of all pilots with their 3 letter code).
What I would like to do now is that I a create a certain matrix in a report which shows the following
Pilotcode Cessna Citation Mustang Falcon
AAA 1:00
So rows in this matrix are all Pilot
so columns are all different typeratings
and values are all Total off block time in hours per pilot and per typerating.
Above I apply a time slicer for the certain period I will select.
The tricky thing is that in the column crewstring two of more pilots are included and in the pilottable only one.
So what I want Power BI to do is search the flightdata for each individual pilot in the crewstring and summarize all blocktime by each individual typerating.
So far I have not been able to succeed in that.
Could someone helpp me out with this?
Thanks!
