cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
C090902/08/2019
CR56002/08/2019
CE45502/08/2019
C090909/08/2019
CR56009/08/2019
CF43309/08/2019
 
Expected Table:
 
KEY PO LINERECURRENCEDate Cancellation Report
C0909202/08/2019 - 09/08/2019
CR560202/08/2019 - 09/08/2019
CE455102/08/2019
CF433109/08/2019

 

Thanks for your help!

Kind regards

Watou

 
11 REPLIES 11
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

Try:

Date Cancellation Report =
VAR _Line = 'Outstanding PO Query'[KEY PO LINE]
RETURN
    _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:

 

LINE

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:

LINE

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

 

Here is the formula I typed:

 

Group By Cancellation =
ADDCOLUMNS(
    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.

Thanks

Super User III
Super User III

Hi,

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

=COUNTROWS(Data)

=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.


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

Hi,

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,

 

Hi,

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


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

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.

Sébastien

Hi,

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


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

Helpful resources

Announcements
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!

secondImage

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