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 ticke...
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

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

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks kentyler , much appreciated :)