Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

 
Need Help

11 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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,
       
  • SteveCampbell's avatar
    SteveCampbell
    Icon for Memorable Member rankMemorable Member
    7 years ago

    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  
    Read my blogs on  

    Remember to spread knowledge in the community when you can! 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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,

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

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