Group By and Concatenate

[Anonymous]

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 LINE	Date Cancellation Report
C0909	02/08/2019
CR560	02/08/2019
CE455	02/08/2019
C0909	09/08/2019
CR560	09/08/2019
CF433	09/08/2019

Expected Table:

KEY PO LINE	RECURRENCE	Date Cancellation Report
C0909	2	02/08/2019 - 09/08/2019
CR560	2	02/08/2019 - 09/08/2019
CE455	1	02/08/2019
CF433	1	09/08/2019

Thanks for your help!

Kind regards

Watou

[Ashish_Mathur]

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]

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 Line	Concatenate	# Nb Lines
C0225912	26/07/2019- 31/07/2019- 02/08/2019- 09/08/2019	4
C08322445	26/07/2019- 31/07/2019	2

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]

Hi,

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

[Anonymous]

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

[Ashish_Mathur]

Hi,

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

[Anonymous]

Hi Ashish,

I will be using the same as my initial post.

I am just wondering if there is a quick way to create a new table using the GROUP BY function that will create the 2 new columns: "Count of recurrences" + "Concatenate Date cancellation report"

Or is the only way to get this result through measures?

Thanks

Sample Table Cancellation (this is a filtered table from a fact table, not a query):

KEY PO LINE	Date Cancellation Report
C0909	02/08/2019
CR560	02/08/2019
CE455	02/08/2019
C0909	09/08/2019
CR560	09/08/2019
CF433	09/08/2019

Expected Table:

KEY PO LINE	RECURRENCE	Concatenate Date Cancellation Report
C0909	2	02/08/2019 - 09/08/2019
CR560	2	02/08/2019 - 09/08/2019
CE455	1	02/08/2019
CF433	1	09/08/2019

[Ashish_Mathur]

Hi,

That is what my formula (already shared with you) does.

[SteveCampbell]

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]

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

[Greg_Deckler]

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

[Anonymous]

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	RECURRENCE	Date Cancellation Report
C0909	2	02/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	RECURRENCE	Date Cancellation Report
C0909	2	02/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,