Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |