Displaying Two Fields in One Column

#1

Hello,

I’m attempting to do something in Power BI Desktop that I’ve been able to do in SSRS but I can’t seem to figure it out.

I work in the Aviation industry and so, for a given flight, I would like to have a column in my report table for crew. This column, per flight, would display the pilot’s name and the co-pilot’s name below that.

Presently, I can do this:
FlightNumber PilotName CoPilotName
ABC123 John Doe Fred Smith

What I’d like to do:
FlightNumber CrewMembers
ABC123 John Doe
Fred Smith

I’m either missing something basic in using Power BI Desktop or, perhaps, I need to be using Power BI Report Builder in order to accomplish this…

Any and all advice will be greatly appreciated. Thanks!

#2

@mschindelman,

I’m sure that there are a number of different ways in Power Query/Power BI to do this, but here’s the approach I took:

This is the sample data I brought in from Excel:

image

  1. pulled this into Power Query, selected flight number and then “Unpivot Other Columns”
  2. renamed the second and third columns in the unpivoted data “Role” and “Name”
  3. created the following measure to concatenate the pilot and copilot for each flight with a hard return in between. UNICHAR(10) is the code for hard return.

Roster List =

    CONCATENATEX(
        'Flight Roster',
        'Flight Roster'[Name],
        UNICHAR( 10 )
    )
  1. created a table visual, and just dropped the Flight Number field and the measure created above into the table Values wells.

Here’s what it looks like all pulled together.

image

Hope this is helpful. Original Excel data and full solution file attached below.

  • Brian

Pilot Copilot.xlsx (8.4 KB)
eDNA Forum - Pilot CoPilot List Solution.pbix (24.3 KB)

1 Like
#3

@BrianJ,

Thanks for the response. I’ll give it a try to see if this works for me. I probably should have mentioned that we’re using SQL Server and trying to keep everything Direct Query for its refresh properties but I’ll play with this next week and see if it will do the trick. Thanks, also, for supplying the files.

1 Like
#4

Hello @mschindelman,

Knowing that you’re getting data in Direct Query Mode from SQL made me think of another possible solution: you could build a View in SQL with all the data that you need and add there, as a separate column, the concatenation of the Picolt and CoPilot .

If you liked my solution please give it a thumbs up :+1:.

If I did answer your question please mark my post as a solution :white_check_mark:.

Thank you !

Cristian

#5

Hi @mschindelman, we’ve noticed that no response has been received from you since the 23rd of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

#6

Hello,
Please note that you have marked this post as solved during an annual three-day Holiday weekend in the USA. In fact, you closed it on a Sunday evening… I have not had the chance to determine whether or not the proposed answer satisfies my issue.
Thanks,

#7

@mschindelman,

Posts that look like they’ve been solved often get tagged with a solution to direct attention/effort to the yet unsolved topics. However, if the solution doesn’t actually work, the original poster always has the ability to remove the “solved” tag and reopen it.

In that case, in addition to untagging it, best practice is also to post a reply indicating that it didn’t work and describing the result received so that the person(s) who replied previously, and perhaps others, can take another crack at it with the new info.

  • Brian
#8

Hi Brian,

Thanks for the explanation; I’m a bit new to this forum.

I have now had the chance to look at your proposed solution. While it wasn’t exactly what I was looking for, your inclusion of the UNICHAR(10) of which I was not aware, proved to be the key to solving my issue.

What I ended up doing was creating a fairly simple DAX measure:
Crew = SELECTEDVALUE(Flight[Captain],"")& UNICHAR(10)& SELECTEDVALUE(Flight[FirstOfficer],"")

This allowed me to maintain the integrity of my report’s Direct Query mode and display the Crew members as desired.

Thanks again for pointing me in the right direction; now, how do I give you accept part of your solution…? :wink:

#9

@mschindelman,

Glad that ultimately helped you get to the desired result.

There’s no way to split credit for a solve, so your final post which provided the desired outcome is credited with the solve. But if someone else’s post (either in the thread or elsewhere) helped you get there, always good form to throw that post a “like” and/or otherwise say thanks, as you did. 99% of the people providing support on the forum are volunteers, so these little things go a long way in making this a great community…

  • Brian
1 Like
#10

Not sure if this is resolved already, but have you tried to the Concatenate function?