Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |