Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I would like to subset the largest date column from each group(id)
ID | Date |
1 | 4/10/2020 |
1 | 4/17/2020 |
1 | 4/29/2020 |
2 | 5/17/2020 |
2 | 12/17/2020 |
My out come should look like this
ID | Date |
1 | 4/29/2020 |
2 | 12/17/2020 |
Var table2 =
DISTINCT(SELECTCOLUMNS( CALCULATETABLE('Table',
FILTER('Table’,NOT('Table’ [Col1]) IN {"Subset1","Subset2”
),
FILTER('Table,'Table[Col2] IN
{"Subset1","Subset2","Subset3",}
),
FILTER('Table,'Table'[Date]>=DATE(YEAR(MAX('Table'[Date]))-1,MONTH(MAX('Table'[ Date])),DAY(MAX('Table'[Date])))
))
,"Col1",'Table'[Col1],"Col2",'Table'[Date],"Col3", 'Table'[Col3]
))
var table3 =
SELECTCOLUMNS(table,"ID",[Col1],"Date",[Date])
Solved! Go to Solution.
@user35131 , You are suing date then it will not reduce the table
filter(SELECTCOLUMNS(table3,"ID",[ID],"date",[date],"Max Date",CALCULATE(MAX([_date]),ALLEXCEPT(table3,[id])))
[Date] =[Max Date])
or
Summarize(Table, Table[ID] , "Date" , Max(Table[Date]) )
The problem in both scripts is that it doesn't pick up on the column name within functions. I'm able to add ID and Date in select columns as a stand alone. Note where its bold i'm having trouble.
filter(SELECTCOLUMNS(table3,"ID",[ID],"date",[date],"Max Date",CALCULATE(MAX([_date]),ALLEXCEPT(table3,[id])))
I found that instead of Summarize(Table, Table[ID] , "Date" , Max(Table[Date]) ) working
that its better to do table 3 as the funneled table and then in the max add the original table not of a variable. I got the result i wanted. Thanky you.
Summarize(Table3, Table3[ID] , "Date" , Max(Table[Date]) )
@user35131 , You are suing date then it will not reduce the table
filter(SELECTCOLUMNS(table3,"ID",[ID],"date",[date],"Max Date",CALCULATE(MAX([_date]),ALLEXCEPT(table3,[id])))
[Date] =[Max Date])
or
Summarize(Table, Table[ID] , "Date" , Max(Table[Date]) )
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
63 | |
53 | |
39 | |
25 |
User | Count |
---|---|
85 | |
57 | |
45 | |
43 | |
38 |