Forum Discussion
Helper IICreate grouped table from date field
I want to create a table from another table. This table must group the records whose unique record must be preserved based on a date field. In the table you see John in double, my result should be that John of team Sweden is kept, because that date has the highest value.
| Name | Team | Date |
| John | Team Polen | 01-01-2019 |
| John | Team Sweden | 01-01-2021 |
| Fred | Team Holland | 01-01-2020 |
| Jack | Team Belgium | 01-01-2020 |
| Steve | Team Germany | 01-01-2021 |
The DAX formula for creating a new calculated table must return the result below. (The John from Team Sweden must stay, because the date field of John from Team Poland was before the date of John from Team Sweden).
| Name | Team | Date |
| John | Team Sweden | 01-01-2021 |
| Fred | Team Holland | 01-01-2020 |
| Jack | Team Belgium | 01-01-2020 |
| Steve | Team Germany | 01-01-2021 |
Thanks in advance for the help!
Please use this table expression.
NewTable =
ADDCOLUMNS (
DISTINCT ( Team[Name] ),
"Date",
CALCULATE (
LASTNONBLANK (
Team[Date],
MIN ( Team[Date] )
)
),
"Team",
CALCULATE (
LASTNONBLANKVALUE (
Team[Date],
MIN ( Team[Team] )
)
)
)Pat
Table=FILTER(SampleData,SampleData[Date]=CALCULATE(MAX(SampleData[Date]),ALLEXCEPT(SampleData,SampleData[Name])))
4 Replies
- mahoneypat
Microsoft Employee
Please use this table expression.
NewTable =
ADDCOLUMNS (
DISTINCT ( Team[Name] ),
"Date",
CALCULATE (
LASTNONBLANK (
Team[Date],
MIN ( Team[Date] )
)
),
"Team",
CALCULATE (
LASTNONBLANKVALUE (
Team[Date],
MIN ( Team[Team] )
)
)
)Pat
brief001Thank you very much for your quick response. I've tested your formula and it works. And personally beautiful, I understand your formula too.
- wdx223_Daniel
Community Champion
Table=FILTER(SampleData,SampleData[Date]=CALCULATE(MAX(SampleData[Date]),ALLEXCEPT(SampleData,SampleData[Name])))
- brief001
Thank you for also posting this answer. The formula works differently from the answer given earlier. But the result is the same. And I also understand this formula now that I read it.