Forum Discussion
Helper IIcolumn with Maximum value with filter condition
Hi Team,
I have two columns ID and Actual Start Date in direct query.
I need another column to get the maximum of Actual Start date where ID = 1000
| ID | Actual Start date |
| 1000 | 1-Jan-21 |
| 2000 | 2-Jan-21 |
| 30000 | 3-Jan-21 |
| 4000 | 4-Jan-21 |
| 1000 | 3-Jan-21 |
| ID | Actual Start date | Max |
| 1000 | 1-Jan-21 | 3-Jan-21 |
| 2000 | 2-Jan-21 | 3-Jan-21 |
| 30000 | 3-Jan-21 | 3-Jan-21 |
| 4000 | 4-Jan-21 | 3-Jan-21 |
| 1000 | 3-Jan-21 | 3-Jan-21 |
i am trying with MAXX and filter but it is removing the rows.
Please help.
Anonymous
Thank you.
Paramjit
Hi Paramjit ，
Since you are using Direct Query mode to connect to the data source, you cannot create a custom column. This is a limitation of Direct Query connection mode. You can review this official documentation below for more details.
There are also restrictions on creating calculated column as follows:
You can create a measure as suggested by amitchandak :
Measure = MAXX(FILTER(ALLSELECTED('Table'),'Table'[ID]="1000"),[Actual Start date])Best Regards
7 Replies
- amitchandak
Super User
Paramjit , Try measure like
maxx(filter(allselected(Table[ID]), Table[ID] =1000), Table[Actual Start date])
or
calculate(max(Table[Actual Start date]), filter(allselected(Table[ID]), Table[ID] =1000 ))
or
calculate(max(Table[Actual Start date]), filter(all(Table[ID]), Table[ID] =1000 ))
ParamjitHi amitchandak
All the below three measures are not working.
Actual start date is not find.
Maxx(filter(allselected(Table[ID]), Table[ID] =1000), Table[Actual Start date])
or It is returing only rows where ID=1000
calculate(max(Table[Actual Start date]), filter(allselected(Table[ID]), Table[ID] =1000 ))
or it is returing rows where ID=1000
calculate(max(Table[Actual Start date]), filter(all(Table[ID]), Table[ID] =1000 ))
Thank you,
Paramjit
- amitchandak
Paramjit , I tried these two seem to be working
Measure = CALCULATE(max(ID1000[Actual Start date]), ID1000[ID]=1000) Measure 2 = CALCULATE(max(ID1000[Actual Start date]), filter(all(ID1000[ID]), ID1000[ID]=1000))file attached after signatures
