Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Group By and Concatenate

Hello everyone,


I am trying to count the number of recurrences over weeks and also create a new column which concatenate the dates of the report when the data appeared. 


From a fact table, I created a filter table:


Table Cancellation = filter('Outstanding PO Query',and('Outstanding PO Query'[Date Cancellation Report] <> "",'Outstanding PO Query'[Line To Cancel] = "YES"))


From this new table, I used the GROUP BY function:


Group By Cancellation = GROUPBY('Table Cancellation','Table Cancellation'[Key PO Line],
"Count Recurrence",COUNTX(CURRENTGROUP(),count('Table Cancellation'[Key PO Line])))
I am getting 2 columns however I would like to have a 3rd column that gives me the concatenate of the "Date Cancellation Report" in the same table with a DAX formula since it is not a query.
Finally, how can I display the data in a visual that only contains a recurrence > 1 and the concatenate value contains the latest "Date Cancellation Report".
Sample Table Cancellation:
KEY PO LINEDate Cancellation Report
Expected Table:
KEY PO LINERECURRENCEDate Cancellation Report
C0909202/08/2019 - 09/08/2019
CR560202/08/2019 - 09/08/2019


Thanks for your help!

Kind regards


Super User IV
Super User IV

For adding a concatenation column, just wrap your GROUPBY with an ADDCOLUMNS function.


@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User I
Super User I


Date Cancellation Report =
VAR _Line = 'Outstanding PO Query'[KEY PO LINE]
     & IF (
            'Outstanding PO Query'[RECURRENCE] > 1,
              " - "
              & CALCULATE (
                    MAX ( 'Outstanding PO Query'[Date Cancellation Report] ),
                    ALL ( 'Outstanding PO Query' ),
                    'Outstanding PO Query'[KEY PO LINE] = _VAR
     BLANK ()





Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png

Remember to spread knowledge in the community when you can! tu.png

Did I answer your question? Mark my post as a solution! Proud to be a Super User!

Connect with me!
Stay up to date on  
Read my blogs on  

Thanks for your reply. I am still novice in Power BI and cannot get what I want. I am getting several times the same data in the Concatenate while I would like only 1 data for each cancellation date. This is an example of what I got:



RECURRENCEDate Cancellation Report
C0909202/08/2019 - 02/08/2019 - 02/08/2019 - 02/08/2019 - 09/08/2019- 09/08/2019- 09/08/2019


I would like something similar to this:


RECURRENCEDate Cancellation Report
C0909202/08/2019 - 09/08/2019


Here is the formula I typed:


Group By Cancellation =
    GROUPBY('Table Cancellation','Table Cancellation'[Key PO Line],
        "Count Recurrence",COUNTX(CURRENTGROUP(),count('Table Cancellation'[Key PO Line]))),"Concatenate",CONCATENATEX('Table Cancellation','Table Cancellation'[Date Cancellation Report],"-"))
Thanks for your help,

Thanks Steve, however I am not sure to get it.

Should I do this calculation from the fact table? What will this give to me?

Sorry I am still novice in BI.


Super User III
Super User III


Drag Key PO Line to the Row labels and write these measures:


=MIN(Data[Date Cancellation Report])&if(MAX(Data[Date Cancellation Report])-MIN(Data[Date Cancellation Report])=0,BLANK()," - "&MAX(Data[Date Cancellation Report]))

Hope this helps.

Ashish Mathur


Thank you. Actually, the countrows does the job by counting the number of recurrences.

I used this Concatenate function as following

Concatenate = CONCATENATEX('Table Cancellation','Table Cancellation'[Date Cancellation Report],"- ",'Table Cancellation'[Key PO Line])


I got this table:

Key PO LineConcatenate# Nb Lines
C022591226/07/2019- 31/07/2019- 02/08/2019- 09/08/20194
C0832244526/07/2019- 31/07/20192


I would like to show data in this table only when the latest date (for instance 09/08/2019 in this example) is included in the concatenate column.

Is it possible to go around with this with a measure or filter?


Thanks again,



I'm not sure of who you are replying to.  Did you try out my solution?

Ashish Mathur

Hello Ashish,


Sorry for my late reply, I was away for a couple of weeks.


I tried out your solutions and I could get the number of recurrences with the COUNTROWS and also get the range MIN-MAX with the report date. However, would it be possible:


- to have the full range of dates displayed (not only MIN & MAX)

- to only display the data that contains the filtered date in the slicer


Otherwise, would it be possible to get this result directly from the table by Grouping by: KEY PO LINE, New column "Count Recurrence" and new column "Concatenate" that concatenates all the cancellation date reports?


Thanks again for your help.



You are picking up this thread after a month.  Please show the dataset and the expected result.

Ashish Mathur

Helpful resources

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!


Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors