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

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.