Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Employee
Employee

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
Employee
Employee

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!

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.