counting instances where logic occurs on 2 rows

[Data-Papa]

employee	InstID	DateTime	transactionID	InstanceName	Date
7	O222	30/05/2023 09:11	YWJPM	View product succeeded	30-May-23
8	O222	11/09/2023 10:32	YWJKN	View product succeeded	11-Sep-23
4	O111	14/06/2023 20:24	YWJH84	View product failed	14-Jun-23
2	O222	21/07/2023 07:39	YWJA24	View product succeeded	21-Jul-23
3	O222	05/04/2023 09:19	YWJ7V5	View product succeeded	05-Apr-23
3	O333	05/04/2023 09:20	YWJ7V5	Update product failed	05-Apr-23

I'm trying to create a measure where I count all the occurences of 'transcationID' "YWJ7V5" occurs; the employee has done step 1 ('InstID' "O222") and viewed the product, and step 2 ('InstID' "O333") failed to update the product. 

I want to count on 'transcationID', so that the count would be 1.

For context, I am trying to create multiple measures so that I can track where the failure has occured on a journey when making changes to a product in the database. Not sure if I should be created a calculated column?

Many thanks in advance.

[FreemanZ · Accepted solution]

hi @Data-Papa ,

Not sure if i fully get you, try to plot a card visual with a measure like:

measure = 
VAR _list =
FILTER(
    VALUES(data[transactionID]),
    VAR _list = CALCULATETABLE(VALUES(data[InstID]))
    VAR _diff = EXCEPT({"O222", "O333"}, _list) 
    RETURN ISEMPTY(_diff)
)
VAR _result1 = COUNTROWS(_list)
VAR _result2 = CONCATENATEX(_list, data[transactionID], ", ") //for test only
RETURN  _result1

it worked like:

[Data-Papa]

thanks @ThxAlot, but this isn't quite what I'm looking for; your DAX is looking for a specific transactionid. The table was an example, but there are thousands of transactions that I'm trying to account for when this happens.  I also don't need to concatenate the count of employees.

I just need to count the amount of times a transaction has occurred, where an employee has viewed the product succesfully, but failed to update the product. 

I've updated the table below to make the example clearer. Now the count would be 2 because of transactions YWJ7V5 && 94GGG

employee	InstID	DateTime	transactionID	InstanceName	Date
7	O222	30/05/2023 09:11	YWJPM	View product succeeded	30-May-23
8	O222	11/09/2023 10:32	YWJKN	View product succeeded	11-Sep-23
4	O111	14/06/2023 20:24	YWJH84	View product failed	14-Jun-23
2	O222	21/07/2023 07:39	YWJA24	View product succeeded	21-Jul-23
3	O222	05/04/2023 09:19	YWJ7V5	View product succeeded	05-Apr-23
3	O333	05/04/2023 09:20	YWJ7V5	Update product failed	05-Apr-23
12	O222	05/10/2023 09:45	LLAFGH	View product succeeded	05-Oct-23
6	O222	17/09/2023 09:45	94GGG	View product succeeded	17-Sep-23
6	O333	17/09/2023 09:46	94GGG	Update product failed	17-Sep-23
6	O222	13/01/2023 11:13	6HYT3	View product succeeded	13-Jan-23
5	O222	15/01/2023 11:33	PAS33	View product succeeded	15-Jan-23
9	O222	05/02/2023 11:13	JGADG	View product succeeded	05-Feb-23
9	O444	05/02/2023 11:14	JGADG	Update product succeeded	05-Feb-23

thanks again, appreciate your help

[FreemanZ · Accepted solution]

hi @Data-Papa ,

Not sure if i fully get you, try to plot a card visual with a measure like:

measure = 
VAR _list =
FILTER(
    VALUES(data[transactionID]),
    VAR _list = CALCULATETABLE(VALUES(data[InstID]))
    VAR _diff = EXCEPT({"O222", "O333"}, _list) 
    RETURN ISEMPTY(_diff)
)
VAR _result1 = COUNTROWS(_list)
VAR _result2 = CONCATENATEX(_list, data[transactionID], ", ") //for test only
RETURN  _result1

it worked like:

[Data-Papa]

hi @FreemanZ ,

That almost works, thanks! It's still not quite right though.

I need "_result1" to bring back all the 'data[transactionID]' where "O222, O333" is applicable only. At the moment, it is bringing back 'data[transactionID]' rows where there is "O222, O333" and others. To clarify I will edit the table again:

employee	InstID	DateTime	transactionID	InstanceName	Date
7	O222	30/05/2023 09:11	YWJPM	View product succeeded	30-May-23
8	O222	11/09/2023 10:32	YWJKN	View product succeeded	11-Sep-23
4	O111	14/06/2023 20:24	YWJH84	View product failed	14-Jun-23
2	O222	21/07/2023 07:39	YWJA24	View product succeeded	21-Jul-23
3	O222	05/04/2023 09:19	YWJ7V5	View product succeeded	05-Apr-23
3	O333	05/04/2023 09:20	YWJ7V5	Update product failed	05-Apr-23
12	O222	05/10/2023 09:45	LLAFGH	View product succeeded	05-Oct-23
6	O222	17/09/2023 09:45	94GGG	View product succeeded	17-Sep-23
6	O333	17/09/2023 09:46	94GGG	Update product failed	17-Sep-23
6	O222	13/01/2023 11:13	6HYT3	View product succeeded	13-Jan-23
5	O222	15/01/2023 11:33	PAS33	View product succeeded	15-Jan-23
9	O222	05/02/2023 11:13	JGADG	View product succeeded	05-Feb-23
44	O222	17/03/2023 11:45	HGHA3	View product succeeded	30-Mar-23
44	O333	17/03/2023 11:46	HGHA3	Update product failed	30-Mar-23
44	O777	17/03/2023 11:46	HGHA3	Delete product failed	30-Mar-23

In the above example, we can see data[transactionid] "HGHA3" has "O222" and "O333", but also "O777". I therefore do not want to count this and the '_result1' should still return 2.

I just need to count the number of rows of data[transactionid] where data[InstID] is "O222" && "O333", but Power BI won't let me for some reason!

Thanks again.

[FreemanZ]

hi @Data-Papa ,

try like:

measure3 = 
VAR _list =
FILTER(
    VALUES(data[transactionID]),
    VAR _list = CALCULATETABLE(VALUES(data[InstID]))
    VAR _diff = UNION( EXCEPT({"O222", "O333"}, _list), EXCEPT(_list, {"O222", "O333"}))
    RETURN ISEMPTY(_diff)
)
VAR _result1 = COUNTROWS(_list)
VAR _result2 = CONCATENATEX(_list, data[transactionID], ", ")
RETURN  _result1

it worked like:

[FreemanZ · Accepted solution]

or this shall also work:

measure4 = 
VAR _list =
FILTER(
    VALUES(data[transactionID]),
    VAR _list2 = CALCULATETABLE(VALUES(data[InstID]))
    VAR _diff =  EXCEPT({"O222", "O333"}, _list2)
    RETURN COUNTROWS(_list2)=2 && ISEMPTY(_diff) 
)
VAR _result1 = COUNTROWS(_list)
VAR _result2 = CONCATENATEX(_list, data[transactionID], ", ")
RETURN  _result1

[Data-Papa]

fantastic, that's got it thanks @FreemanZ !

Interestingly though, it does seem to throw up an 'exceeds resources' error on occasion; might there have been an alternative, more optimal way to achieve the same result?


I was toying with SUMMARIZE, creating columns with IF statements as to whether the row had specific InstIDs and then creating a measure off the back of that, an iterator to return the count of rows where the flag criteria of the respective column was met. 

Not sure if it would have been better for performance to do something like this?

[ThxAlot]