count if but then in power BI

[HJA1976]

Hi, 

I am struggling with the following. I would like to count the hours a person has flow on certain types.

I have three datasources: 

1) datasource flights:  flights including flight time and pilots (one data field two pilots in this datafield, each pilot has three characters)

2) one datasource : pilots: all pilots (each pilot also three characters)

3) one datasource : type rating: all aircrafts sorted by type rating. 

How can visualize by pilot all flown hours pro aircrafttype.

so for pilot ABC I would like to check if there is a match in the column flight [ flightcrew] with the three characters in the datasource pilots. 

All hours should be sorted by aircraft type. in the flight datasource there is a column with aircraft registration. In the datasource typerating there is a translation of registration <=> to typerating. 

I have made the relations. 

So I am struggling with how to make a measure or calculation or a formule to get out the right matrix. 

Now it show the following matrix but I want it for each pilot and not for the flight crew. The tricky thing for me is that each row delivers for example flight time for pilot A and for pilot B. 

Look forward to your reactions!

[lc_finance]

Hi @HJA1976 ,

Can you share a sample for your Power BI file and an example of the numbers you want to see for 2-3 pilots?

That will help in better understanding your data model.

You can upload files and then paste a link using DropBox, One Drive, Drive or another similar tool

LC

[HJA1976]

Hi LC,

appreciate your help!

I have created a testfile, see below link in dropbox

https://www.dropbox.com/home/Henrik/Power%20BI

I would like to visualize all pilots individually in a matrix and show for these pilots the hours flown on a specific typerating.

For me I have two challenges:

1) each flight has two involved pilots, that are included in the crewstring of the flight datasource. The pilot datasource only shows all pilots individually

2) how to aggregate the flight time. I cannot use aggregate for this datafield

Look forward to your help!

[lc_finance]

Hi @HJA1976 ,

I tried accessing it but it says the folder does not exist. Do you want to try re-uploading it?

LC

[HJA1976]

Hi LC, see link below. Does it work now?

https://www.dropbox.com/s/mg11nf6k7ogt2c6/TESTFILE%2013%20OCTOBER%202019.pbix?dl=0

[lc_finance]

Hi @HJA1976 

Yes, it works now.

You can find below the solution I propose:

1) create a calculated table.

  - here I split the pilot column into 2 (one column for each pilot), and then I unpivot it. With the unpivot, the final table only has one row per pilot

  - I also recalculate the time as a number instead of a time, I found out this is necessary to add up the flight times. For example, I convert 1 hour 30 minutes into the number 1.5 hours.

2) create a measure to visualize the times
  - the measure reconverts back from a number (example: 1.5 hours) into a time: 1 hour 30 minutes

Here is the formula for the calculated table:

Flight by pilot = UNION(
    SELECTCOLUMNS('Flights',"aircraft", [aircraft], "Date", [Date], "flight crew", [flight crew], "flightid", [flightid], "flighttime", [flighttime],
    "flighttimeHours",HOUR([flighttime])+MINUTE([flighttime])/60, "pilot", LEFT([flight crew],3))
    , SELECTCOLUMNS('Flights',"aircraft", [aircraft], "Date", [Date], "flight crew", [flight crew], "flightid", [flightid], "flighttime", [flighttime], 
    "flighttimeHours",HOUR([flighttime])+MINUTE([flighttime])/60,"pilot", RIGHT([flight crew],3))
)

Here is the formula for the measure:

flighttimeHoursMinutes = 
VAR flighttimeTotal = SUM('Flight by pilot'[flighttimeHours])
VAR flighttimeOnlyHours = ROUNDDOWN(flighttimeTotal,0)
VAR flighttimeOnlyMinutes = ROUNDDOWN((flighttimeTotal-flighttimeOnlyHours)*60,0)

RETURN
TIME(flighttimeOnlyHours,flighttimeOnlyMinutes,0)

And below a screenshot with the final result:

And as it's a bit complicated, I also attached the final Power BI file:

https://drive.google.com/file/d/11zQeWcjS-uINzgGZx5rWiUJiKtKYarxH/view?usp=sharing

Let me know if it works for you!

LC

Interested in Power BI finance templates? Check out my blog at www.finance-bi.com

[HJA1976]

Hi LC, great job!!! Many thanks!!!

I love it, but I would have never found the answer myself. Whats your approach for this normally? Especcially this Union function, how do you find that out? This was the testfile. Would it be possible to share with you the bigger file and do the same.  Do you have a personal email address for me?

I first try it myself and I f dont succeed i come back!@

[lc_finance · Accepted solution]

Hi @HJA1976 ,

I'm glad this works for you!

You can select my reply as the answer to your question so other users with similar questions might find it.

For the UNION approach, it was shared here in the Power BI community by @Greg_Deckler :

https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832

If you want to discuss more, you can send me a direct message here in the PBI Community (mail button at the top right)

LC

[HJA1976]

Just some background information about the data and the data relatons