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

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

mhutchens81 Member
Member

Re: Summary table with calculated columns

Thanks @kentyler , much appreciated 🙂

mhutchens81 Member
Member

Re: Summary table with calculated columns

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

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors