Forum Discussion
Resolver IConcatenate 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.
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.
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
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],",")Regards,
Xiaoxin Sheng
17 Replies
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],",")Regards,
Xiaoxin Sheng
P3TomHelper I
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
Hello Anonymous
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
- Ashish_Mathur
Super User
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],",")
- P3Tom
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.
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
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:
- 2016
- 2014
- 2015, 2016
- 2015, 2016, 2017
Say I added in the following years
- 2016
- 2016
- 2014
- 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
- Ashish_Mathur
Hi,
Just drag the Year column to the Row labels and then write this measure
=COUNTROWS(Data)
Hope this helps.
lsuresh89Hi 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!
