Combine text from multiple columns into one

[NourJ]

Dear great cummunity,

i have Country1, Country2, Country3

and beside each column one column for the number of individual 

i need to disply them in multi-row card as one value without duplication

is there a way to do so? 

[Greg_Deckler]

UNION the VALUES of your columns together and then do a DISTINCT perhaps. 

[NourJ]

@Greg_Decklerhow i can union them, i forgot to mention that this data is being updated mothly. so how can i union them ? is there a formula to do it or i had to add extra table manualy?

[v-jiascu-msft]

Hi @NourJ ,

Can you post the expected result based on your snapshot, please? Would like the unique value in a column or unioning the three columns?

Best Regards,

[NourJ]

This is the result that i want to get, i want to have a column have the value of the three columns to usein slicer and in the multi-row card. this picture from old data, at that time i have it in one column while now i have to update my dashboard in the same visualization but the source of data have changed to three columns.

is it possible to have it like the attached pic?!

[v-jiascu-msft · Accepted solution]

Hi @NourJ ,

There are two solutions. Please download the demo from the attachment.

1. Transform the data using M.

Result = Table.Combine({Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Col1", "Value1"}), {{"Col1", "Col"}, {"Value1", "Value"}}), 
Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Col2", "Value2"}), {{"Col2", "Col"}, {"Value2", "Value"}}), 
Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Col3", "Value3"}), {{"Col3", "Col"}, {"Value3", "Value"}})})

2. Create a new table and establish three relationships.

Cols =
FILTER (
    DISTINCT (
        UNION (
            VALUES ( Table2[Col1] ),
            VALUES ( Table2[Col2] ),
            VALUES ( Table2[Col3] )
        )
    ),
    [Col1] <> BLANK ()
)

Measure =
SUM ( Table2[Value1] )
    + CALCULATE (
        SUM ( Table2[Value2] ),
        USERELATIONSHIP ( Cols[Col1], Table2[Col2] )
    )
    + CALCULATE (
        SUM ( Table2[Value3] ),
        USERELATIONSHIP ( Cols[Col1], Table2[Col3] )
    )

Best Regards,

[NourJ]

@v-jiascu-msftthank you so much it works with the second one