Forum Discussion
counting instances where logic occurs on 2 rows
| 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.
hi Anonymous ,
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 _result1it worked like:
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
7 Replies
- ThxAlot
Super User
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
FreemanZhi Anonymous ,
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 _result1it worked like:
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.
