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

Measure to record the absence of strings in an audit log

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':

CustomerRefNumActivityTypeDateCreatedAnalystTicketContainsSearch
Apple1Opened01/09/2019 10:00amJimYes
Apple1Comment01/09/2019 10:01amJimYes
Apple1Search01/09/2019 10:02amJimYes
Apple1Search01/09/2019 10:03amBarryYes
Apple2Opened01/09/2019 10:04amBarryYes
Apple2Comment01/09/2019 10:05amBarryYes
Apple2Comment01/09/2019 10:06amBarryYes
Apple2Search01/09/2019 10:07amJimYes
Microsoft3Opened01/09/2019 10:08amJaneNo
Microsoft3Comment01/09/2019 10:09amSallyNo
Microsoft3Comment01/09/2019 10:10amJaneNo
Microsoft3Comment01/09/2019 10:11amJaneNo
Google4Opened01/09/2019 10:12amJaneYes
Google4Search01/09/2019 10:13amSallyYes
Google4Comment01/09/2019 10:14amJaneYes
Google4Search01/09/2019 10:15amJaneYes
Microsoft5Opened01/09/2019 10:17amPeterNo
Microsoft5Comment01/09/2019 10:18amJimNo
Microsoft5Comment01/09/2019 10:19amJimNo

 

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

1 ACCEPTED SOLUTION
jdbuchanan71
Super User II
Super User II

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" )

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

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User III
Super User III

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Nathaniel_C
Super User I
Super User I

Hi @Anonymous ,
Contains.PNG

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

jdbuchanan71
Super User II
Super User II

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" )

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

View solution in original post

Anonymous
Not applicable

That's perfect 🙂 Thank you so much 🙂

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors