cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhutchens81 Helper III
Helper III

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

Accepted Solutions
Microsoft Phil_Seamark
Microsoft

Re: Summary table with calculated columns

Hi @mhutchens81 

 

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
Super User II
Super User II

Re: Summary table with calculated columns

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!




Microsoft Phil_Seamark
Microsoft

Re: Summary table with calculated columns

Hi @mhutchens81 

 

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

Highlighted
mhutchens81 Helper III
Helper III

Re: Summary table with calculated columns

Thanks @kentyler , much appreciated 🙂

mhutchens81 Helper III
Helper III

Re: Summary table with calculated columns

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors