0

I have the following table in which I am trying to the extract the Status based on the Latest Date

--------------------------------------
   Date    | User |  Status  | ID |
--------------------------------------
12-01-2019 |  A   | Approved | 1A |
14-01-2019 |  A   | Approved | 1A |    
21-02-2019 |  B   | Rejected | 2B |
26-02-2019 |  B   | Approved | 2B |
--------------------------------------

I have used the following DAX

Max_date = Calculate(max(table1[Date]),(ALLEXCEPT(table1,table1[User],table1[ID]))

Result = Calculate(max(table1[Status]), FILTER(ALLEXCEPT(table1,table1[User],table1[ID]),
                   table1[Date]= table1[Max_date]))

Which gives me the following result

---------------------------------------------------------
   Date    | User |  Status  | ID | Max_Date  | Result  |
---------------------------------------------------------
12-01-2019 |  A   | Approved | 1A |14-01-2019 |Approved |
14-01-2019 |  A   | Approved | 1A |14-01-2019 |Approved |
21-02-2019 |  B   | Rejected | 2B |26-02-2019 |Approved |
26-02-2019 |  B   | Approved | 2B |26-02-2019 |Approved |
----------------------------------------------------------

My table2,

------------------------------
| ID    |  Car     | Type    |
------------------------------
|  1A   | Benz     | A-class |
|  1B   | Benz     | B-class |    
|  1C   | Benz     | C-class |
|  1B   | BMW      | M1      |  
|  2B   | BMW      | M2      |
------------------------------

I have defined the relationship with table1 and table2. [Mapped ID to ID in both tables] When I add Car and Type to my table, the data bulges (Similar to a cross join).

--------------------------------------------------------------------------
   Date    | User |  Status  | ID | Max_Date  | Result  | Car  | Type    |
--------------------------------------------------------------------------
12-01-2019 |  A   | Approved | 1A |14-01-2019 |Approved | Benz | A-Class |
14-01-2019 |  A   | Approved | 1A |14-01-2019 |Approved | Benz | A-Class |
21-02-2019 |  B   | Rejected | 2B |26-02-2019 |Approved | Benz | B-Class |
26-02-2019 |  B   | Approved | 2B |26-02-2019 |Approved | Benz | B-Class |
12-01-2019 |  A   | Approved | 1A |14-01-2019 |Approved | BMW  | M1      |
14-01-2019 |  A   | Approved | 1A |14-01-2019 |Approved | BMW  | M2      |
21-02-2019 |  B   | Rejected | 2B |26-02-2019 |Approved | BMW  | M1      |
26-02-2019 |  B   | Approved | 2B |26-02-2019 |Approved | BMW  | M2      |
--------------------------------------------------------------------------

My Desired output,

   Date    | User |  Status  | ID | Max_Date  | Result  | Car  | Type    |
--------------------------------------------------------------------------
12-01-2019 |  A   | Approved | 1A |14-01-2019 |Approved | Benz | A-Class |
14-01-2019 |  A   | Approved | 1A |14-01-2019 |Approved | Benz | A-Class |
21-02-2019 |  B   | Rejected | 2B |26-02-2019 |Approved | BMW  | M1      |
26-02-2019 |  B   | Approved | 2B |26-02-2019 |Approved | BMW  | M2      |
kcvizer
  • 137
  • 1
  • 2
  • 8

1 Answers1

0

You can use measures to calculate what you need:

Last Date = CALCULATE(LASTDATE(myTable[Date]), ALL(myTable[Date],myTable[Status]))

Last Status = LOOKUPVALUE(myTable[Status], myTable[Date], [Last Date])

Note: if you have a calendar table, use the date column of the calendar.

Giovanni Luisotto
  • 1,382
  • 7
  • 12