Forum Discussion
Helper IPower BI count distinct IDs with a condition
I have a data that looks like this:
| Date | TicketId | Step | Result |
| 01.01.2020 13:00:00 | 123456 | 2 | Successfull |
| 01.01.2020 13:00:00 | 123456 | 4 | Successfull |
| 01.01.2020 13:00:05 | 123456 | 8 | Error |
| 01.01.2020 13:05:00 | 123456 | 2 | Successfull |
| 01.01.2020 13:05:00 | 123456 | 4 | Successfull |
| 01.01.2020 13:05:05 | 123456 | 8 | Error |
| 01.01.2020 13:10:00 | 123456 | 2 | Successfull |
| 01.01.2020 13:10:00 | 123456 | 4 | Successfull |
| 01.01.2020 13:10:05 | 123456 | 8 | Error |
| 01.01.2020 13:15:00 | 123456 | 2 | Successfull |
| 01.01.2020 13:15:00 | 123456 | 4 | Successfull |
| 01.01.2020 13:05:00 | 654321 | 2 | Successfull |
| 01.01.2020 13:05:00 | 654321 | 4 | Successfull |
| 01.01.2020 13:05:05 | 654321 | 8 | Error |
| 01.01.2020 13:10:00 | 654321 | 2 | Successfull |
| 01.01.2020 13:10:00 | 654321 | 4 | Successfull |
| 01.01.2020 13:10:05 | 654321 | 8 | Error |
| 01.01.2020 13:15:00 | 654321 | 2 | Successfull |
| 01.01.2020 13:15:00 | 654321 | 4 | Successfull |
| 01.01.2020 13:10:00 | 098765 | 2 | Successfull |
| 01.01.2020 13:10:00 | 098765 | 4 | Successfull |
| 01.01.2020 13:10:05 | 098765 | 8 | Error |
| 01.01.2020 13:10:00 | 098765 | 2 | Successfull |
| 01.01.2020 13:10:00 | 098765 | 4 | Successfull |
| 01.01.2020 13:10:05 | 098765 | 8 | Error |
From that data I have a matrix that calculates the distinct quantity of TicketIDs that have a step = 8 (Result = 'Error').
I need a DAX measure to calculate distinct number of TicketIDs if the TicketID have the Result = 'Successfull' as a last, before he had a Result = 'Error'. Note that next I will need to drillthrogh through the mesaure up to found TicketId.
Example of the desired output:
| Date | Count_distinct_TickedId_with_Errors | Count_distinct_TickedId_with_Successfull_and_Errors_before |
| 01.01.2020 | 3 | 2 |
It's a 123456 and 654321 TickedIds.
Current DAX measure to calculate distinct TickedIds with errors looks like this:
- LogDistcount_ = DISTINCTCOUNT(Logging[TicketId])
- Errors_dist = CALCULATE(Logging[LogDistcount_], Logging[Step] = 😎
And also, if it's not hard, the measure to calculate the TickedId, which is still have an Error result as at last.
| Date | Count_distinct_TickedId_with_Errors | Count_distinct_TickedId_with_Successfull_and_Errors_before | Count_distinct_TickedId_with_Errors_still |
| 01.01.2020 | 3 | 2 | 1 |
It's a 098765.
Many thanks!
1 Reply
Hi bombom ,
Depending on your requirements, you want to implement a measure [calculate distinct number of TicketIDs if the TicketID have the Result = 'Successfull' as a last], but I didn't fully understand the logic you want to output after looking at your Example of the desired output.
If it is convenient, can you write your Example of the desired output and populate it in the reply so that we can fully understand your needs?
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
