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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
khappersett
Resolver I
Resolver I

Concatenate Columns If Same ID

I have a table with rows for item ID, catalog name, and catalog year. Some items are released in multiple catalogs so they have multiple rows. I am trying to create a new measure that will concatenate all the years for each item. For example...

 

 

Item ID          Catalog Name         Catalog Year

A                          Spring                   2015

A                          Spring                   2016

A                          Spring                   2017

B                          Fall                        2015

B                          Fall                        2016

B                          Fall                        2017

 

And I want a new measure that would add this to the table.....

 

Item ID          Catalog Name         Catalog Year         All Catalog Years

A                          Spring                   2015                 2015, 2016, 2017

A                          Spring                   2016                 2015, 2016, 2017

A                          Spring                   2017                 2015, 2016, 2017

B                          Fall                        2014                 2014, 2016, 2017

B                          Fall                        2016                 2014, 2016, 2017

B                          Fall                        2017                 2014, 2016, 2017

 

 

This is my current DAX code, but when I use it in a visual I am getting the message "Can't display the visual"

 

All Years = CONCATENATEX('Catalog Dict', 'Catalog Dict'[yr], VALUES('Catalog Dict'[item_id]), ", ")

I have also tried the following, but it did not give me the correct results.

 

All Years = CONCATENATEX('Catalog Dict', 'Catalog Dict'[yr], ", ")

Ultimately, I am trying to find a way to count the items that are in a catalog for one year, but not another. So count items that were in a 2017 catalog, but not in a 2016 catalog. Couldn't think of an easy way to do that, so thought I could make a new measure (or perhaps a calculated column) that would return all catalog years for each item and then do a DISTINCTCOUNT with a filter checking if the new calculated column contains "2017" but not "2016, for example. Any help is appreciated.

2 ACCEPTED SOLUTIONS
P3Tom
Helper I
Helper I

Since your data set did not have any items that were in only 1 year, I created a similar set which you can find in this linked pbix file.

 

Items in Catalog Years.png

 

Multiple table model is required to get the results above.

 

Here are the measures:

 

Count If Item Sold in Only 1 Year =
COUNTROWS ( FILTER ( 'Items', [Item Years in Catalog] = 1 ) )

Item Catalog Years =
IF (
    NOT ( ISBLANK ( SELECTEDVALUE ( Items[Item ID] ) ) ),
    CONCATENATEX ( RELATEDTABLE ( Data ), Data[Catalog Year], ", " )
)

Item Total Years in Catalog =
CALCULATE (
    COUNTROWS ( VALUES ( 'Years'[Year] ) ),
    CROSSFILTER ( Data[Catalog Year], Years[Year], BOTH )
)

 

Tom

www.powerpivotpro.com

 

View solution in original post

v-shex-msft
Community Support
Community Support

Hi @khappersett,

 

I think you need to filter the records which has the same id, then use this as the source of concatenate function.

All Year = CONCATENATEX(FILTER(ALL('sample'),[Item ID]=EARLIER('sample'[Item ID])),[Catalog Year],",")

8.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

17 REPLIES 17
v-shex-msft
Community Support
Community Support

Hi @khappersett,

 

I think you need to filter the records which has the same id, then use this as the source of concatenate function.

All Year = CONCATENATEX(FILTER(ALL('sample'),[Item ID]=EARLIER('sample'[Item ID])),[Catalog Year],",")

8.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

This worked for my use case.  I extended this with an additional condition to exclude a field value from being included in the concatenation.

All Year = CONCATENATEX(FILTER(ALL('sample'),[Item ID]=EARLIER('sample'[Item ID]) && [Item ID]<>"Not Supplied"),[Catalog Year],",")

Hello @v-shex-msft 

 

What if additionally you have for the same Item ID duplicate Catalog Year? How could you consider in your formula only the unique values? I mean, if item ID "A" have in column "Catalog Year" the values 2012,2012 and 2014 i just want to consider in "All year" this result: 2012,2014. 

 

Thanks,

filipe197

Hi @filipe197,

 

Do you want that as a calculated column formula or as a measure?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur

As a column.

Hi,

 

I have done this using Power Query and then pushed the data into the Data Model.  Will this be OK with you?

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello @Ashish_Mathur

I can see on your sample that item B has in catalogue year column the value of 2014 twice and in column All Year you have made the concatenation without repeating 2014. That's what I need. What's the formula you have set for this?

Hi @filipe197,

 

You may refer to my solution here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur

 

I dont use power query and even if i want to, i can't install it in my pc 😞 can you just tell me the dax formula you've written to get de outcome of "All Year" column?

 

Many thanks,

filipe197

Hi,

 

Power Query comes with the Power BI desktop (Home > Edit Queries.  The Query Editor window opens up is Power Query).  Download the PowerBI desktop solution from here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Although this topic is titled, "Concatenate Columns If Same ID", later in the original request it says:

 

"Ultimately, I am trying to find a way to count the items that are in a catalog for one year, but not another."

 

Restated, concatenation was a means used to count the items that are in a catalog only for one year, but not another.

 

A complete solution will also give an answer to the ultimate end (what is the count of catalog items that were listed for only one year).

 

Tom

P3Tom
Helper I
Helper I

Since your data set did not have any items that were in only 1 year, I created a similar set which you can find in this linked pbix file.

 

Items in Catalog Years.png

 

Multiple table model is required to get the results above.

 

Here are the measures:

 

Count If Item Sold in Only 1 Year =
COUNTROWS ( FILTER ( 'Items', [Item Years in Catalog] = 1 ) )

Item Catalog Years =
IF (
    NOT ( ISBLANK ( SELECTEDVALUE ( Items[Item ID] ) ) ),
    CONCATENATEX ( RELATEDTABLE ( Data ), Data[Catalog Year], ", " )
)

Item Total Years in Catalog =
CALCULATE (
    COUNTROWS ( VALUES ( 'Years'[Year] ) ),
    CROSSFILTER ( Data[Catalog Year], Years[Year], BOTH )
)

 

Tom

www.powerpivotpro.com

 

Hi @P3Tom, This is an awesome solution. I just have an additional requirement which I'm unable to figure out. Could you help me with a solution to only show unique years in case I have a repitition of years. E.g If in the table above we have A+Spring+2015 twice, it should still give me only one instance while showing "Item catalog years" which in the example above would be 2015,2016,2017 and not 2015,2015,2016,2017.

 

Could you please help me with a way to come up with distinct values? Thanks!

Anonymous
Not applicable

@P3Tom 

I concatenated values by ID number and now I am trying to figure out how to get a distinct count of similar concatenated fields.

 

For example, this is the following outcome:

  1. 2016
  2. 2014
  3. 2015, 2016
  4. 2015, 2016, 2017

Say I added in the following years

  1. 2016
  2. 2016
  3. 2014
  4. 2015, 2016

I want to be able to get a count of the unique concatenated fields:

2016

3

2014

2

2015, 2016

2

2015, 2016, 2017

1

 

Any help would be appreciated!

Thanks

Hi,

Just drag the Year column to the Row labels and then write this measure

=COUNTROWS(Data)

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur ,

 

To better understand what I’m doing, below are two tables.

 

Using this formula, I was able to concatenate the different paths the residents took:

 

Continuum = IF ( NOT ( ISBLANK ( SELECTEDVALUE ( Episode[ResidentSys] ) ) ), CONCATENATEX( RELATEDTABLE( 'Unit Stay Table' ), 'Unit Stay Table'[Unit], ", " ) )

 

The table output that I got looks similar to this (I am using the matrix visualization):

Resident Number

Paths

1

L

2

L, L, A, A

3

R, L, A

4

A, I

5

A, I

6

I

7

I

8

I

9

A

10

A

11

L, R

12

L, R

 

 

I want to be able to do a “count” of the different unique paths to get an output like this:

Path

Number of Residents

L

1

L, L, A, A

1

R, L, A

1

A, I

2

I

3

A

2

L, R

2

 

 

Thank you so much for all your help!! I really appreciate it 😊

Hi,

I will not be able to help you with this.  Someone else will.  Sorry.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.