Hi PBI Community,
I am working on an interesting problem. I have two tables A and B.
Table A: Contains a list of transactions with a transaction number, item, date and status.
Table B: Contains the list of items that is unique.
Table B has a 1 to many relationship with table A.
Objective: Count the number of elements in Table A based on values in other columns within and display as a calculated column in table B.
Criteria:
For the highest Transaction number for each item in table A:
If the due date is after 5 January AND if status is A or B
Count +1 for corresponding item in table B.
Data and expected result:
| TABLE A |
| Transaction Number | Item | Due Date | Status |
| 1 | Item1 | 23-Jan | A |
| 2 | Item1 | 25-Feb | B |
| 3 | Item1 | 10-Jan | C |
| 1 | Item2 | 11-Jan | A |
| 1 | Item3 | 11-Jan | A |
| 2 | Item3 | 11-Jan | C |
| 4 | Item3 | 13-Jan | B |
| 4 | Item3 | 02-Jan | A |
| 1 | Item4 | 01-Jan | C |
| 1 | Item1 | 15-Jan | A |
| 1 | Item4 | 16-Jan | B |
| 1 | Item5 | 17-Jan | B |
| 1 | Item5 | 17-Jan | A |
| TABLE B |
| Item | Result |
| Item1 | 0 |
| Item2 | 1 |
| Item3 | 1 |
| Item4 | 1 |
| Item5 | 2 |
Eg1. Item 1 has 4 transactions, the highest Transaction number is 3 with a due date of 10 Jan and Status C, so result is 0 in Table B.
Eg2. Item 2 has 1 transaction, with a due date of 11 January with a status A so result in Table B is 1.
Eg3. Item 5 has 2 transactions of the same number with the same due date but different statuses A and B so result in table B is 2.
Where I've gotten to: I've managed to flag the max transaction number for each item in table A.
Max Transaction = CALCULATE(MAX(Table A[Transaction Number]),ALLEXCEPT(Table A,Table A[Item]))
Any one have any ideas on how to obtain the column Result in table B based on these criteria?
Thanks,
Dilip
