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.
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!
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Table=FILTER(SampleData,SampleData[Date]=CALCULATE(MAX(SampleData[Date]),ALLEXCEPT(SampleData,SampleData[Name])))
Table=FILTER(SampleData,SampleData[Date]=CALCULATE(MAX(SampleData[Date]),ALLEXCEPT(SampleData,SampleData[Name])))
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you very much for your quick response. I've tested your formula and it works. And personally beautiful, I understand your formula too.
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |