Concatenate Text with Measure not Custom Column

[o59393]

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.

Can you please help me getting the concat right?

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

This DAX formula works

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

Hope this helps.

[carlovsky]

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

Any suggestion?

Thanks

Diego

[Ashish_Mathur]

Hi,

Explain the question and show the expected result.

[carlovsky]

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:

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:

Output in a table:

Any solution for this?

Thank you

[Ashish_Mathur]

Hi,

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

[Greg_Deckler]

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

[o59393]

hi @Greg_Deckler 

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

Thanks.

[Greg_Deckler]

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?

[o59393]

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.

[Greg_Deckler]

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],", ")

[o59393]

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

Thanks for the help @Greg_Deckler 

[Greg_Deckler]

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

[o59393]

Hi @Greg_Deckler 

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

 

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.

[Greg_Deckler]

@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))

[Greg_Deckler]

@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. 

[Greg_Deckler · Accepted solution]

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

Column =

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

[Ashish_Mathur · Accepted solution]

Hi,

This DAX formula works

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

Hope this helps.