Measure to record the absence of strings in an audit log

[Anonymous]

Hi folks, I'm hoping someone can assist.

I have a table of data (example below), that contains some Reference Numbers for customers, as well as audit log entries relating to each RefNum, datestamps for when those audit log entries occurred, and the 'Analyst' who performed each Activity. Note that the data may appear in any order.

The column in bold is the measure I'm hoping someone can help me create - it records, for each grouping of 'RefNum', whether a 'Search' ActivityType string is present. If yes, the column should record 'Yes', and if 'No', the column should record 'No':

Customer	RefNum	ActivityType	DateCreated	Analyst	TicketContainsSearch
Apple	1	Opened	01/09/2019 10:00am	Jim	Yes
Apple	1	Comment	01/09/2019 10:01am	Jim	Yes
Apple	1	Search	01/09/2019 10:02am	Jim	Yes
Apple	1	Search	01/09/2019 10:03am	Barry	Yes
Apple	2	Opened	01/09/2019 10:04am	Barry	Yes
Apple	2	Comment	01/09/2019 10:05am	Barry	Yes
Apple	2	Comment	01/09/2019 10:06am	Barry	Yes
Apple	2	Search	01/09/2019 10:07am	Jim	Yes
Microsoft	3	Opened	01/09/2019 10:08am	Jane	No
Microsoft	3	Comment	01/09/2019 10:09am	Sally	No
Microsoft	3	Comment	01/09/2019 10:10am	Jane	No
Microsoft	3	Comment	01/09/2019 10:11am	Jane	No
Google	4	Opened	01/09/2019 10:12am	Jane	Yes
Google	4	Search	01/09/2019 10:13am	Sally	Yes
Google	4	Comment	01/09/2019 10:14am	Jane	Yes
Google	4	Search	01/09/2019 10:15am	Jane	Yes
Microsoft	5	Opened	01/09/2019 10:17am	Peter	No
Microsoft	5	Comment	01/09/2019 10:18am	Jim	No
Microsoft	5	Comment	01/09/2019 10:19am	Jim	No

If someone were able to assist, I'd really appreciate it!

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[RefNum]=EARLIER(Data[RefNum])&&Data[ActivityType]="Search"))>0,"Yes","No")

Hope this helps.

[Nathaniel_C]

Hi @Anonymous ,

new measure =
CALCULATE (
    IF ( CONTAINS ( 'Table', 'Table'[ActivityType], "Search" ), "Yes", "No" ),
    ALLEXCEPT ( 'Table', 'Table'[RefNum] )
)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Anonymous]

Thanks for the quick response @Nathaniel_C , I really appreciate it 🙂

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

Give this a try as a calculated column.  You would just need to change the name of your table in the measure.

TicketContainsSearch = 
VAR _Count = CALCULATE ( COUNTROWS ( YourTable ), ALLEXCEPT ( YourTable, YourTable[RefNum] ), YourTable[ActivityType] = "Search" )
RETURN IF ( _Count > 0, "Yes","No" )

 If this solves your issues please mark it as the solution. Kudos 👍 are nice too.

[Anonymous]

That's perfect 🙂 Thank you so much 🙂