Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Summary table with calculated columns

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:

 

RefNumActivityTypeDateActivityCreated
1054976Search19/02/2019 9:59
1054976Link19/02/2019 9:59
1054675Reopened19/02/2019 9:58
1054675Resolved19/02/2019 9:59
1054675Field Updated30/04/2019 8:44
1054675Field Updated11/07/2019 12:12
1251685Other11/07/2019 12:11
1290806Search7/02/2019 14:09
1290806Field Updated7/02/2019 14:05
1290806Field Updated7/02/2019 14:07
1290806Link7/02/2019 14:06
1290806Other7/02/2019 14:06
1290806Reopened7/02/2019 14:05
1290806Resolved7/02/2019 14:09
1290806Status Changed7/02/2019 14:06
1294302Search15/03/2019 14:40
1296581Search11/03/2019 13:57
1296581Field Updated11/03/2019 13:57
1296581Field Updated11/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:

 

RefNumSearchLinkKnowledge GapNo Search No Link
1054976SearchLink  
1054675   No Search No Link
1251685   No Search No Link
1290806SearchLink  
1294302Search Knowledge Gap 
1296581Search Knowledge Gap 

 

Any help would be greatly appreciated!

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    6 years ago

    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

4 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    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

     

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Perfect, thanks Phil_Seamark . That worked a treat :) Appreciate the quick response.