Forum Discussion

katyfailoo's avatar
katyfailoo
Icon for Advocate I rankAdvocate I
3 years ago
Solved

Return Unique values from concat two columns

I have this formula (Tables are masked) in which returns concatenated values for two columns in Table A that unfortunately does not return unique values. Would someone be able to assist on how this can be written so that my results are unique?

 

Combined Values =

CALCULATE(CONCATENATEX(, 
FILTER(
'Table_A',
'Table_A'[Key]
IN VALUES('Table_B'[Key])
),
'Table_A'[Column_1]&" - "&'Table_A'[Column_2],
";"& UNICHAR(10),
'Table_A'[Column_1], ASC
), ALL('Table_A'))

DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi katyfailoo 

    please try

    Combined Values =
CALCULATE (
    CONCATENATEX (
        DISTINCT (
            SELECTCOLUMNS (
                FILTER ( 'Table_A', 'Table_A'[Key] IN VALUES ( 'Table_B'[Key] ) ),
                "@_1", 'Table_A'[Column_1],
                "@_2", 'Table_A'[Column_2]
            )
        ),
        [@_1] & " - " & [@_2],
        ";" & UNICHAR ( 10 ),
        [@_1], ASC
    ),
    ALL ( 'Table_A' )
)

2 Replies

  • katyfailoo's avatar
    katyfailoo
    Icon for Advocate I rankAdvocate I
    3 years ago

    Thank you so much! tamerj1 this solution works. Can't thank you enough for your time and help!

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi katyfailoo 

    please try

    Combined Values =
CALCULATE (
    CONCATENATEX (
        DISTINCT (
            SELECTCOLUMNS (
                FILTER ( 'Table_A', 'Table_A'[Key] IN VALUES ( 'Table_B'[Key] ) ),
                "@_1", 'Table_A'[Column_1],
                "@_2", 'Table_A'[Column_2]
            )
        ),
        [@_1] & " - " & [@_2],
        ";" & UNICHAR ( 10 ),
        [@_1], ASC
    ),
    ALL ( 'Table_A' )
)