cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION
Phil_Seamark
Microsoft
Microsoft

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Phil_Seamark
Microsoft
Microsoft

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Anonymous
Not applicable

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

kentyler
Solution Sage
Solution Sage

So I created a dummy table, here's the calculated column "knowledge gap"knowledge_gap.pngthe 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

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

Thanks @kentyler , much appreciated 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.