How to avoid duplicate values when using ConcatenateX

[lafakios]

Hi,

I am using the below method:

AllDates = CONCATENATEX (

 FILTER (

 ALL ( Table1 ), 

Table1[Item] = Table2[Item]  

),

 Table1[YYYY-MM],

 " , "

)

and in some cases I get as result the below result:

"2024.02 , 2024.02 , 2024.03 , 2024.03 , 2024.04 , 2024.04 , 2024.05 , 2024.05 , 2024.06 , 2024.07 , 2024.08 , 2024.09"

What I would like to get instead is:

"2024.02 , 2024.03 , 2024.04 , 2024.05 , 2024.06 , 2024.07 , 2024.08 , 2024.09"

I tried using something like the below version and it did not work

AllDates = CONCATENATEX (

 FILTER (

 ALL ( Table1 ),

 Table1[Item] = Table2[Item]  &&  Table1[YYYY-MM] <> EARLIER( Table1[YYYY-MM] )  

 ),

 Table1[YYYY-MM],

 " , "

)

Would you know if there is anyway to produce the required result?

Thanks in advance for your help.

Regards,

Akis

[barritown]

Hi @lafakios,

You should leave only unique values first with the help of VALUES or DISTINCT and then concatenate them.

Like that:

In plain text:

AllDates = CONCATENATEX ( CALCULATETABLE ( VALUES ( Table1[YYYY-MM] ), Table1[Date] < DATE ( 2014, 1, 1 ) ), Table1[YYYY-MM], ", " )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[lafakios]

The above suggestion cannot unfortunately be used in this case.

The item is a kind of key (which is not unique at Table1 but is unique at Table2). 

The item is found in Table 1 several times with a different value of YYYY-MM.

In Table2 which has only the distinct values of the items, for each item I would like to have in an column a summary of all YYYY-MM values that are present at the various entries of that item in Table1. 

Please note that the YYYY-MM values are handled as text in both tables.

That's why I am using the expression: Table1[Item] = Table2[Item] 

[barritown · Accepted solution]

@lafakios,

It would be easier to tackle your case having a mocking example of your dataset. 

Please check the attached file - maybe it is what you need.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[lafakios]

Hi,

This did deliver the wanted result, thank you a lot for your help!

I am also providing below the suggested formula of the solution:

YYYY-MM =

VAR curItem = [Item]

RETURN CONCATENATEX ( CALCULATETABLE ( VALUES ( Table1[YYYY-MM] ), Table1[Item] = curItem ), [YYYY-MM], ", " )