Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
4 | Opened | 01/09/2019 10:12am | Jane | Yes | |
4 | Search | 01/09/2019 10:13am | Sally | Yes | |
4 | Comment | 01/09/2019 10:14am | Jane | Yes | |
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!
Solved! Go to 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.
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.
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
Proud to be a Super User!
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.
That's perfect 🙂 Thank you so much 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |