I was hoping to get a bit of assistance with this problem I am having. I would like to include a calculated column that references another column from another table; preferably the max of a number.
The data looks like this at the moment:
- Customer Service Cases
| Case Number | Date Closed |
|---|---|
| 11111 | 31/12/2021 |
| 22222 | 1/1/2022 |
| 33333 | 3/1/2022 |
- Case History
This table shows the case history of every case and how it moves between statuses. The activity index states the order of when each one occurred, if there is one that has occurred at the same time, the same number is shared.
| Case Number | Activity Index | Value | Classification | Time Stamp |
|---|---|---|---|---|
| 11111 | 1 | Marketing | Case Cue | 31/12/2021 15:00:00 |
| 11111 | 2 | Finance | Case Cue | 31/12/2021 18:00:00 |
| 11111 | 2 | Pending | Status | 31/12/2021 18:00:00 |
| 11111 | 3 | HR | Case Cue | 3/3/2022 18:00:00 |
| 22222 | 1 | IT | Case Cue | 1/1/2022 15:00:00 |
| 22222 | 1 | Pending | Status | 1/1/2022 15:00:00 |
| 33333 | 1 | Open | Status | 3/1/2022 12:00:00 |
| 33333 | 2 | HR | Case Cue | 3/1/2022 13:34:00 |
| 33333 | 3 | Finance | Case Cue | 3/1/2022 13:38:00 |
| 33333 | 4 | HR | Case Cue | 3/1/2022 15:27:00 |
| 33333 | 4 | Closed | Status | 3/1/2022 15:27:00 |
My Desired Output:
I would like to get a calculated column in the ‘Fact Table’ which displays the latest value (I’ve used activity number to determine the latest) with those rows which has ‘Case Cue’.
For example:
| Case Number | Date Closed | Desired Output |
|---|---|---|
| 11111 | 31/12/2021 | HR |
| 22222 | 1/1/2022 | IT |
| 33333 | 3/1/2022 | HR |
I have tried using this DAX formula:
Desired Output =
VAR MaxIndex = CALCULATE( MAX(‘CaseHistory’[Activity Index]), ‘CaseHistory’[Classification] = “Case Cue”))
RETURN
CALCULATE(
SELECTEDVALUE(‘CustomerServiceCases’[Value]),
‘CaseHistory’[Activity Index] = MaxIndex)
This formula has worked for the most part however, it does not seem to work once the activity index is the same number and I just get blanks…
Would like your assistance on how I could tackle this. Your help is greatly appreciated.
Please see file attached which contains mock data:
EDNA - Test.pbix (54.2 KB)
