Power BI count distinct IDs with a condition

[bombom]

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:

1. LogDistcount_ = DISTINCTCOUNT(Logging[TicketId])
2. 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!

[v-tangjie-msft]

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.