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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
lafakios
Helper I
Helper I

How to avoid duplicate values when using ConcatenateX

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

View solution in original post

4 REPLIES 4
barritown
Super User
Super User

Hi @lafakios,

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

Like that:

barritown_0-1727969542607.png

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

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] 

@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

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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