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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
NourJ
Helper III
Helper III

Combine text from multiple columns into one

Capture1.PNGDear great cummunity,Capture.PNG

 

 

i have Country1, Country2Country3

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? 

1 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] )
    )

Combine-text-from-multiple-columns-into-one

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

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



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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

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.3.PNG 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?!

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

Combine-text-from-multiple-columns-into-one

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.