Forum Discussion
How to create table based on minimum date from other table
Hi Folks,
I want to create second table from first table using filters with dates and other variables as follows. How to create this ?
Follwowing is the expected table and original table
| Original Table | ||||||
| Index | Category | Value_1 | Value_2 | Date | ||
| 1 | A | 0.06 | 1.00 | 1/3/2019 | Minimum Date | |
| 2 | A | -0.1 | 4.00 | 1/4/2019 | ||
| 3 | A | 0.03 | 58.00 | 1/5/2019 | ||
| 4 | A | 0.1 | 63.00 | 1/6/2019 | ||
| 5 | D | 0.02 | 64.26 | 1/3/2019 | ||
| 6 | D | -0.02 | 62.97 | 1/4/2019 | ||
| 7 | D | -0.09 | 57.31 | 1/5/2019 | ||
| 8 | D | 0.04 | 59.60 | 1/6/2019 | ||
| DESIRED TABLE | ||||||
| Index | Category | Value_1 | Value_2 | Date | ||
| 1 | A | 0.06 | 1.00 | 1/3/2019 | ||
| 5 | D | 0.02 | 1.02 | 1/3/2019 | ||
Anonymous
You may use the following DAX to add a calculated table.
Table = FILTER ( Table1, RANKX ( FILTER ( Table1, Table1[Category] = EARLIER ( Table1[Category] ) ), Table1[Date], , ASC, DENSE ) = 1 )
5 Replies
Anonymous you don't need new table for this you can achieve this by measure, one thing is not clear from your exmple, why value_2 for D will be 1.02 instead of 64.26 from table A
Anonymous
You may use the following DAX to add a calculated table.
Table = FILTER ( Table1, RANKX ( FILTER ( Table1, Table1[Category] = EARLIER ( Table1[Category] ) ), Table1[Date], , ASC, DENSE ) = 1 )
