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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
o59393
Post Prodigy
Post Prodigy

Concatenate Text with Measure not Custom Column

Hi all

 

I am trying to concatenate 2 text columns with a dax measure, not with query editor's custom column.

 

I used the dax

 

 

Concat = CONCATENATE (
SELECTEDVALUE ( Query1[[L1.3 - Country]]]) & " ",
SELECTEDVALUE ( Query1[[L1.8 - Bev Segment]]] )
)

 

 

The problem is that I have to select the 2 columns in order to make work the formula. If I just drag and drop the measure "concat" by itself in a table it wont work.

 

concat.PNG

 

Can you please help me getting the concat right?

 

Thanks

2 ACCEPTED SOLUTIONS

Hi,

This DAX formula works

Measure = CONCATENATEX(Data,Data[[L1.3 - Country]]]&" "&Data[[L1.8 - Bev Segment]]],UNICHAR(10))

Hope this helps.

Untitled.png


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

View solution in original post

Or, if you just want a calculated column just do this:

 

Column =

CONCATENATE(Query1[[L1.3 - Country]]] & " ",Query1[[L1.8 - Bev Segment]]] )

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

16 REPLIES 16
carlovsky
Helper II
Helper II

Hi

@Ashish_Mathur and @Greg_Deckler 

 

I'm using this measure:

Measure 3 = CONCATENATEX('Dim Supplier Group','Dim Supplier Group'[COD_COUNTRY]&" "&'Dim Supplier Group'[Supplier Group],UNICHAR(10))

 

I'm achieving this result in a matrix table

 

 

carlovsky_1-1646068222965.png

 

carlovsky_0-1646068169758.png
Any suggestion?

 

Thanks

Diego

 

Hi,

Explain the question and show the expected result.


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

Hello @Ashish_Mathur ,

 

I'm looking for a solution to check the highest performing Supplier, no matter the country they are in.

I have a matrix table where I would like to sort for more than one column

 

The problem?
In a matrix I can't sort more than one column, so the result that I will see is just
the highest performer in the Country Selected.

 

What i'm thinking?
In a concatenation of Country Code and Supplier Group in order to get it done.

 

This is my current configuration of my matrix table:

Rows: Country Code(EU,PT,ES), Supplier(Zara,Coca-Cola,Amazon) Brand ( ABC,XYZ,EEE)
Columns:
Year(2021, Month id(202101,202102)
Values:
Measures like Conversion Rate %,Turnover etc

 

Dummy table:

carlovsky_0-1646223458582.png

 

I don't have the chance to create a calculated column so my only way to tackle this is by using a measure:

What am i using:

Measure_concate = 

CONCATENATEX('Dim Supplier Group','Dim Supplier Group'[Supplier Group]&" - "&'Dim Supplier Group'[COD_COUNTRY],UNICHAR(10))
 
Because i can't add the measure as a row, i added them in the vales, and this is what i am getting now:


Output in the matrix:
carlovsky_3-1646223745589.png

 

carlovsky_1-1646223623918.png
 
Output in a table:
carlovsky_2-1646223655339.png

 

Any solution for this?

 

Thank you

Hi,

I still cannot understand much.  The images are way too small.


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

Concat = CONCATENATE (
MAX( Query1[[L1.3 - Country]]]) & " ",
MAX( Query1[[L1.8 - Bev Segment]]] )
)

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

hi @Greg_Deckler 

 

it didnt return all the possible combinatiosn, only 1 value:

 

cocnat.PNG

 

Thanks.

Wait, what is your expected result? That was meant to create the column in your table. I don't understand what you want in your card visual. What is that?

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi,

 

i want to have all the combinations for these 2 columns. Instead of creating a colum, i wanted to see if it could be done with a dax.

 

Is it possible?

 

Thanks.

Difficult to test because sample data was not supplied in text. 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

 

But perhaps this:

Concat =
  VAR __Table =
    ADDCOLUMNS(
      'Table',
      "__Concat",CONCATENATE(MAX( Query1[[L1.3 - Country]]]) & " ",MAX( Query1[[L1.8 - Bev Segment]]] ))
    )
RETURN
  CONCATENATEX(__Table,[__Concat],", ")

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi

 

I got an error, here is the pbix

 

https://1drv.ms/u/s!ApgeWwGTKtFdhj2ATqAvODltNTad?e=UNH425

 

In the code I changed the Table', for  'Query1' and managed to get a few results only

 

Capture.PNG

 

Thanks for the help @Greg_Deckler 

Concat = 
  VAR __Table =
    ADDCOLUMNS(
      'Query1',
      "__Concat",CONCATENATE(Query1[[L1.3 - Country]]] & " ",Query1[[L1.8 - Bev Segment]]] )
    )
RETURN
  CONCATENATEX(__Table,[__Concat],", ")

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler 

 

Almost ok! I see that all the values are in once cell, how can they be separated per row?

 

Capture2.PNG 

The expected result is this one in the excel.

 

https://1drv.ms/u/s!ApgeWwGTKtFdhj7fQfJwcIsMFfcw?e=osMaWm

 

Sorry for this, next time i will include the solution.

 

Thanks a lot.

@o59393 - So like this?

Concat = 
  VAR __Table =
    ADDCOLUMNS(
      'Query1',
      "__Concat",CONCATENATE(Query1[[L1.3 - Country]]] & " ",Query1[[L1.8 - Bev Segment]]] )
    )
RETURN
  CONCATENATEX(__Table,[__Concat],UNICHAR(10))

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@o59393 - If you want it like the Excel file, that was the first solution (with the MAX values) and you add that measure to your table visualization. You don't want a separate visualization in that case. Or am I missing something?

 

See the attached file with both of the possible solutions. 

 


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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Or, if you just want a calculated column just do this:

 

Column =

CONCATENATE(Query1[[L1.3 - Country]]] & " ",Query1[[L1.8 - Bev Segment]]] )

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi,

This DAX formula works

Measure = CONCATENATEX(Data,Data[[L1.3 - Country]]]&" "&Data[[L1.8 - Bev Segment]]],UNICHAR(10))

Hope this helps.

Untitled.png


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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.