Summary table with calculated columns

[Anonymous]

Hi everyone, I'm hoping someone can assist.

I have a table with lots of data - 1 million+ lines. Each line in the table shows a ticket ID (RefNum), an activity that has occurred within each ticket (ActivityType), and a datestamp for that activity (DateActivityCreated). There may be multiple lines for each RefNum, and each line is in no particular order. Example below:

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

I'd like to be able to create a summary table that shows 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 be 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'd expect 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

Any help would be greatly appreciated!

[Phil_Seamark · Accepted solution]

Hi @Anonymous 

You could try

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","")
    )

Here is a link to a PBIX file for you to play with

https://1drv.ms/u/s!AtDlC2rep7a-xiu2Eh71sSW31ous?e=6iku4S

[Anonymous]

Perfect, thanks @Phil_Seamark . That worked a treat 🙂 Appreciate the quick response.

[kentyler]

So I created a dummy table, here's the calculated column "knowledge gap"the search and link columns are partial versions of the same code

Search =

VAR curTicket = [Ticket]

var curTable = filter('table','table'[ticket] = curTicket)

VAR searchCount = Countrows(filter(curTable,[Activity Type]="Search"))

VAR sCount = if(searchCount,searchCount,0)

var returnText = if(sCount>0, "Search","")

RETURN returnText

and 

Link =

VAR curTicket = [Ticket]

var curTable = filter('table','table'[ticket] = curTicket)

VAR linkCount = Countrows(filter(curTable,[Activity Type]="Link"))

var lCount = if(linkCount,linkCount,0)

var returnText = if(lCount>0,"Link","")

RETURN returnText

[Anonymous]

Thanks @kentyler , much appreciated 🙂