Modification to metric: "Summary table with calculated columns"

[Anonymous]

Hi everyone, I'm hoping someone can assist with a modificaton to an existing metric.

I originally had a table with lots of data - 1 million+ lines. Each line in the table showed a ticket ID (RefNum), an activity that occurred within each ticket (ActivityType), and a datestamp for that activity (DateActivityCreated). There were potentially multiple lines for each RefNum, and each line is in no particular order. There is also a 'Customer' column. Example below:

Customer	RefNum	ActivityType	DateActivityCreated
Google	1054976	Search	19/02/2019 9:59
Google	1054976	Link	19/02/2019 9:59
Microsoft	1054675	Reopened	19/02/2019 9:58
Microsoft	1054675	Resolved	19/02/2019 9:59
Microsoft	1054675	Field Updated	30/04/2019 8:44
Microsoft	1054675	Field Updated	11/07/2019 12:12
Apple	1251685	Other	11/07/2019 12:11
Google	1290806	Search	7/02/2019 14:09
Google	1290806	Field Updated	7/02/2019 14:05
Google	1290806	Field Updated	7/02/2019 14:07
Google	1290806	Link	7/02/2019 14:06
Google	1290806	Other	7/02/2019 14:06
Google	1290806	Reopened	7/02/2019 14:05
Google	1290806	Resolved	7/02/2019 14:09
Google	1290806	Status Changed	7/02/2019 14:06
Microsoft	1294302	Search	15/03/2019 14:40
Apple	1296581	Search	11/03/2019 13:57
Apple	1296581	Field Updated	11/03/2019 13:57
Apple	1296581	Field Updated	11/03/2019 13:57

I wanted to be able to create a summary table that showed single instances of each individual RefNum per line, and additional columns for Search, Link, Knowledge Gap, and No Search No Link. Here's how the information in those columns should have been calculated:

• Search = Any ticket that contains at least one 'Search' ActivityType should show the text "Search"
• Link = Any ticket that contains at least one 'Link' ActivityType should show the text "Link"
• Knowledge Gap = Any ticket that contains at least one 'Search' ActivityType and does NOT contain at least one 'Link' ActivityType should show the text "Knowledge Gap"
• No Search No Link = Any ticket that does not contain a 'Search' and 'Link' ActivityType should show the text "No Search No Link"

Here is the data I had expected to see, based on the table above:

RefNum	Search	Link	Knowledge Gap	No Search No Link
1054976	Search	Link
1054675				No Search No Link
1251685				No Search No Link
1290806	Search	Link
1294302	Search		Knowledge Gap
1296581	Search		Knowledge Gap

A kind person gave me this solution, which worked great:

Table 2 = 
ADDCOLUMNS(
SUMMARIZECOLUMNS(
    'Table'[RefNum] ,
    "Search" , IF(COUNTROWS(FILTER('Table','Table'[ActivityType]="Search")),"Search","") ,
    "Link" , IF(COUNTROWS(FILTER('Table','Table'[ActivityType]="Link"))>0,"Link","") 
    ),
    "Knowledge Gap",IF([Search]="Search" && [Link]="","Knowledge Gap",""),
    "No Search No Link" , IF([Search]="" && [Link]="","No Search No Link","")
    )

But I'd like some help in modifying the above metric, so that I get the result below. Basically I want to include the bolded column in the summary table, which wasn't specified in my original request:

Customer	RefNum	Search	Link	Knowledge Gap	No Search No Link
Google	1054976	Search	Link
Microsoft	1054675				No Search No Link
Apple	1251685				No Search No Link
Google	1290806	Search	Link
Microsoft	1294302	Search		Knowledge Gap
Apple	1296581	Search		Knowledge Gap

I'd really appreciate some help!

[JasonTX · Accepted solution]

What happens if you try something like this?

Table 2 = 
ADDCOLUMNS(
SUMMARIZECOLUMNS(
    'Table'[Customer] , 
    'Table'[RefNum] ,
    "Search" , IF(COUNTROWS(FILTER('Table','Table'[ActivityType]="Search")),"Search","") ,
    "Link" , IF(COUNTROWS(FILTER('Table','Table'[ActivityType]="Link"))>0,"Link","") 
    ),
    "Knowledge Gap",IF([Search]="Search" && [Link]="","Knowledge Gap",""),
    "No Search No Link" , IF([Search]="" && [Link]="","No Search No Link","")
    )

[Anonymous]

Much obliged @JasonTX , thank you 🙂