topic How to avoid duplicate values when using ConcatenateX in DAX Commands and Tips

How to avoid duplicate values when using ConcatenateX

lafakios — Thu, 03 Oct 2024 15:31:23 GMT

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

Re: How to avoid duplicate values when using ConcatenateX

barritown — Thu, 03 Oct 2024 15:33:24 GMT

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

Re: How to avoid duplicate values when using ConcatenateX

lafakios — Thu, 03 Oct 2024 18:20:54 GMT

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]

Re: How to avoid duplicate values when using ConcatenateX

barritown — Thu, 03 Oct 2024 18:47:20 GMT

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

Re: How to avoid duplicate values when using ConcatenateX

lafakios — Thu, 03 Oct 2024 19:33:13 GMT

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