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 everyone,
I have a table of data (example below), that contains some Reference Numbers, as well as audit log entries relating to each RefNum. I need to create two measures that gives me a count of the reference numbers that have been 'touched' by each Analyst, based on whether a search has been performed, or not performed, first in the ticket. I've put together some simple user stories & functional requirements, as well as example results I might expect, in the hope that it makes this request easier:
RefNum | ActivityType | DateCreated | Analyst |
1 | Opened | 01/09/2019 10:00am | Jim |
1 | Comment | 01/09/2019 10:01am | Jim |
1 | Search | 01/09/2019 10:02am | Jim |
1 | Search | 01/09/2019 10:03am | Barry |
2 | Opened | 01/09/2019 10:04am | Barry |
2 | Comment | 01/09/2019 10:05am | Barry |
2 | Comment | 01/09/2019 10:06am | Barry |
2 | Search | 01/09/2019 10:07am | Jim |
3 | Opened | 01/09/2019 10:08am | Jane |
3 | Comment | 01/09/2019 10:09am | Sally |
3 | Comment | 01/09/2019 10:10am | Jane |
3 | Comment | 01/09/2019 10:11am | Jane |
4 | Opened | 01/09/2019 10:12am | Jane |
4 | Search | 01/09/2019 10:13am | Sally |
4 | Comment | 01/09/2019 10:14am | Jane |
4 | Search | 01/09/2019 10:15am | Jane |
Results I want to see:
Count of RefNum | Analyst |
2 | Jim |
1 | Barry |
1 | Sally |
1 | Jane |
Results I want to see:
Count of RefNum | Analyst |
2 | Jim |
0 | Barry |
1 | Sally |
0 | Jane |
Notes:
@Anonymous ,
I'm not very clear about "a Search activity first", I suppose that means the first "DateCreated" per Analyst. If so, you can create a Flag column to record which rows can't be count.
Flag = IF ( ( 'Table'[DateCreated] = CALCULATE ( MIN ( 'Table'[DateCreated] ), ALLEXCEPT ( 'Table', 'Table'[Analyst] ) ) ) && ( CALCULATE ( MIN ( 'Table'[DateCreated] ), ALLEXCEPT ( 'Table', 'Table'[Analyst] ) ) = CALCULATE ( MIN ( 'Table'[DateCreated] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Analyst] ), 'Table'[ActivityType] = "Search" ) ) ), "Not Count" )
Then create a measure to count the RefNum as below(However the result is not same with the value you provided in the first senario).
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yuta-msft , thanks very much for your response.
Your solution works well, except that it only seems to consider the first RefNum in any one series, rather than performing the calculation for each RefNum in the range. For example, using your solution, the first RefNum has a "Not Count" in it:
But if I add a new 'RefNum' (in this case, "7") with a new earliest 'DateCreated', the "Not Count" disappears from RefNum "1" and appears in "7":
@Anonymous ,
So the minimun "datecreated" should be grouped by both Analyst and RefNum, right? You can modify the flag column as below:
Flag = IF ( ( 'Table'[DateCreated] = CALCULATE ( MIN ( 'Table'[DateCreated] ), ALLEXCEPT ( 'Table', 'Table'[Analyst], 'Table'[RefNum] ) ) ) && ( CALCULATE ( MIN ( 'Table'[DateCreated] ), ALLEXCEPT ( 'Table', 'Table'[Analyst], 'Table'[RefNum] ) ) = CALCULATE ( MIN ( 'Table'[DateCreated] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Analyst], 'Table'[RefNum] ), 'Table'[ActivityType] = "Search" ) ) ), "Not Count" )
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yuta-msft , my apologies, I've just realised that this isn't quite achieving what I need yet. Almsot there I think!
I've added a screenshot below showing what searches should be flagged as 'Not Count' (tick marks) and what should not be flagged ('X'). The crosses represent searches that were performed first in each RefNum, and therefore need to be excluded. The tick marks represent searches that were all performed second, chronologically in each ticket, and therefore need to be included as 'Not Count':
Thanks for your time, I really appreciate it.
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 |
---|---|
106 | |
98 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |