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

[Anonymous]

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

[camargos88]

@Anonymous ,

You can use this measure:

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

[Pragati11]

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

[Anonymous]

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.

[Pragati11 · Accepted solution]

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

[Greg_Deckler]

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

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

[Anonymous]

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.