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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Concatenate Measure not Custom Column

I need to Concatenate Two text strings for a Matrix to simplify the view. We have a Data Cube that does not allow the addition of Tables or Custom Columns. DAX CONCATENATE is for a custom Column. 

 

I cannot figure out how to do this wirth a Measure. I saw one solution but it basically grabs all data and it is not recommended due to the slowdown of the processing. 

 

Any Ideas?

 

Thank you.

10 REPLIES 10
v-lili6-msft
Community Support
Community Support

Hi@ Moscuba

You can try to this measure as below:

Measure 3 = CONCATENATE (
SELECTEDVALUE ( 'Table2'[PCATID]  )&"_",
SELECTEDVALUE ( 'Table2'[PCAT Name] )
)

Result:

3.PNG

Best Regards,

Lin

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

 

Is it possible to include the final concatenated srting in table, without making use of original string columns inside table from where the final stringw as derived? 

 

This is the same measure i was looking for except i would like to extend it by making it a count.
Said differently I would like the measure to return the count of "Measure 3", in your example, to be used in a Card .

Can this be done ?
Thansk You

Anonymous
Not applicable

This is the perfect answer which I was looking for, Thanks lot for the help. Even on the microsoft own portal they mentioned the Dax function but the syntax is incorrect when we tried using text filed name which is not measures

 

https://docs.microsoft.com/en-us/dax/concatenate-function-dax

=CONCATENATE(Customer[LastName], CONCATENATE(", ", Customer[FirstName]))

Greg_Deckler
Super User
Super User

You can use CONCATENATE in a measure. Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

The Data is Text columns in this case.  When I trierd CONCATENATE it did not work. It refused to use valid table and fields. 

 

Here is the example I saw in the DAX Pages:

 

=CONCATENATE(Customer[LastName], CONCATENATE(", ", Customer[FirstName]))

 

PCATID                   PCAT Name

AAAA                      Sealant 1

AAAB                      Sealant 2

BBBA                      Adheasive1

BBBB                       Adheasive2

 

I'' like to join the two with a space or underscore in between like:

AAAA _Sealant1

 

No Number conversions needed.

Hi,

 

Does this work?

 

=VALUES([PCATID])&"_"&VALUES([PCAT Name])

 

or

 

=CONCATENATEX(Data,VALUES([PCATID])&"_"&VALUES([PCAT Name]))

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

When working with measures, you need to do an aggregation on a column, like MAX:

 

Measure = MAX([PCATID]) & "_" & MAX([PCAT Name]) 

And I would use the & operator instead of CONCATENATE.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

The MAX will not work mwith Text / String. That was the only solution that accepted the fields though. 

 

CONCATENATE Will not accept the fields and I tried ISTEXT. 

 

This is very odd and I'm wondering if it's ther table format. Though, everywhere I look I see that concatenating needs to be done as a Column. 

 

We'll be requesting that we can make columns in the data "cube". 

 

Cheers and thank you.

Anonymous
Not applicable

The MAX will not work mwith Text / String. That was the only solution that accepted the fields though. 

 

CONCATENATE Will not accept the fields and I tried ISTEXT. 

 

This is very odd and I'm wondering if it's ther table format. Though, everywhere I look I see that concatenating needs to be done as a Column. 

 

We'll be requesting that we can make columns in the data "cube". 

 

Cheers and thank you.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.