topic Concatenate values in one line for a identic ID (differents tables) in DAX Commands and Tips

Concatenate values in one line for a identic ID (differents tables)

Anonymous — Mon, 20 Jul 2020 12:51:13 GMT

Hello,

For a table visualization, I would like to get in one line the currencies used in a company/country. Most of them use a unique currency but there is some using more. You can see an example below.

The major problem is that I canno't transform data (no PowerQuery or Column creation) because it's not my perimeter of work in my enterprise. So, I have to create a DAX measure.

Also, the data model used is big and complex but you can see concerned relations below.

What I have :

Company Name	Currency Code
Company 1	EUR
Company 1	SEK
Company 2	EUR
Company 2	CHF
Company 3	EUR
Company 3	NOK

What I want :

Company Name	Currency Code
Company 1	EUR - SEK
Company 2	EUR - CHF
Company 3	EUR - NOK

Relations between tables (short view) :

Company table Revenues table Currency table

Company_ID ------> Company_ID Currency Code

Company Name Currency_ID <------ Currency_ID

Other infos Other infos Other infos

PS : The Company and Currency ID type is numeric (not text)

Thanks in advance

Re: Concatenate values in one line for a identic ID (differents tables)

Greg_Deckler — Mon, 20 Jul 2020 13:02:50 GMT

Anonymous - You shoudl be able to create a measure like:

Measure = CONCATENATEX(DISTINCT('Table'[Currency Code]),[Currency Code]," - ")

Re: Concatenate values in one line for a identic ID (differents tables)

Pragati11 — Mon, 20 Jul 2020 13:08:37 GMT

HI Anonymous ,

Create a caluclated column as follows:

Clubbed value = CONCATENATE('tablename'[Company name], CONCATENATE("-", 'tablename'[Currency code]))

Modify the table name and columns in above expression with your column names.

Move your Comapny Name columns and above created calculated column to a table visual and you will get the desired result.

Thanks,

Pragati

Re: Concatenate values in one line for a identic ID (differents tables)

Anonymous — Tue, 21 Jul 2020 14:52:27 GMT

Thanks for the answer Greg_Deckler

I already tried this measure but the problem is always the same : on the tab, the measure prints me all the currencies even the ones that are not used in the company/country (the entire whole of the currency code column)

like it :

Company Name	Currency Code	Measure for currencies
Company 1	EUR	ADP-AED-AFA-ALL-...-CHF-...EUR-............
Company 1	CHF	ADP-AED-AFA-ALL-...-CHF-...EUR-............

I already try a lot of differents measures like this :

Currencies = VAR __DISTINCT_VALUES_COUNT = CALCULATE(DISTINCTCOUNT('Revenues'[FkId_Currency]),FILTER(Revenues, Revenues[FkId_Currency]<> -1))

VAR __MAX_VALUES_TO_SHOW = 3

Return

IF( __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW

,CONCATENATE(

CONCATENATEX(

TOPN( __MAX_VALUES_TO_SHOW, DISTINCT('Currency'[Currency Code]),'Currency'[Currency Code], DESC ),

'Currency'[Currency Code], "/", 'Currency'[Currency Code], DESC )

, "/..." ),

IF( __DISTINCT_VALUES_COUNT=1, CONCATENATEX( VALUES('Currency'[Currency Code]), 'Currency'[Currency Code],"", 'Currency'[Currency Code], DESC ),

CONCATENATEX( DISTINCT('Currency'[Currency Code]), 'Currency'[Currency Code], "/", 'Currency'[Currency Code], DESC

)))

The result is the same

I'll come back to you if I find any clue about why I got this result.

Re: Concatenate values in one line for a identic ID (differents tables)

Anonymous — Tue, 21 Jul 2020 14:53:25 GMT

Thanks for the answer Pragati11

Unfortunately, as I said before I can't transform datsa because I'm connected to an Azure cube and that is not my perimeter of work. So, I can't create column from it, I only can use and manipulate datas.

Re: Concatenate values in one line for a identic ID (differents tables)

camargos88 — Tue, 21 Jul 2020 15:23:39 GMT

Anonymous ,

You can use this measure:

Measure = CONCATENATEX(VALUES('Table'[Currency Code]), 'Table'[Currency Code], "-")

Re: Concatenate values in one line for a identic ID (differents tables)

Pragati11 — Tue, 21 Jul 2020 15:25:26 GMT

Hi Anonymous ,

Try creating the following measure then:

Clubbed value Measure= CONCATENATEX('tablename', 'tablename'[Company name] & 'tablename'[Currency code], ", ")

Modify the table name and columns in above expression with your column names.

Thanks,

Pragati