Join 2 fields in a PowerBI Measure - no access to create in Model Table

[awilson26]

Hi All,

I only have access to create reports from an existing online Model within PowerBI. I am unable to create an additional column in the model that would concatenate these fields easily.

I have 4 fields that I want to concatenate into a single field being:

- FirstName

- Surname

- Shortname.

- A " - " also needs to be included which is not inc the model.

Example of fields inPeopleInformationTable at present is:

FirstName   Surname    ShortName

Barry           Star             BStar

Desired Outcome in one NEW Measure that would be inserted into a Table in a report is:

Barry Star - BStar

Is there a way in PowerBI to join these fields without requiring an update to the underlying model? 

 Cheers,

Amanda

[danextian · Accepted solution]

Hi @awilson26 

Measures do not have a row context. For example, if you use SELECTEDVALUE('table'[First Name]) & SELECTEDVALUE('table'[Last Name]) within a measure but do not include the referenced columns in your visual, you will see a blank result. If modifying the model isn't an option, you can create a local model by converting a live connection to direct query. This approach is triggered when you add an additional data source. Alternatively or if the direct quey approach still isn't an option, you can request the semantic model owner to add the necessary information for you.

[amitchandak]

@awilson26 , You can create new measure but that will work as expected only when you have first name and last name in visual. You need to have column for that

Measure  = Maxx(Table, Left([First Name], 1)  & [Last Name] )

Better to have a column = Left([First Name], 1)  & [Last Name]

[johnt75]

As long as you have at least 1 column in the table visual which uniquely identifies a row in the person table, you could use

Concatenated Name =
SELECTEDVALUE ( PeopleInformation[FirstName] ) & " "
    & SELECTEDVALUE ( PeopleInformation[Surname] ) & " - "
    & SELECTEDVALUE ( PeopleInformation[ShortName] )

The column to identify a row doesn't necessarily need to be visible. You could turn off word wrap for that column and set the width to 0.