Return Unique values from concat two columns

[katyfailoo]

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

[tamerj1 · Accepted solution]

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

[katyfailoo · Accepted solution]

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

[tamerj1 · Accepted solution]

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