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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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