max function for date within calculated column

[Anonymous]

calculated column in import method =

 Maxdate= CALCULATE(MAX(' Table1'[CommonDate]),FILTER('table1','table1'[ID]=EARLIER('table1'[ID])),FILTER(ALLSELECTED('table1'[created calulated category column]),'table1'[created calulated category column]="category1"|| ''table1'[created calulated category column]="category2"||'table1'[created calulated category column]="category3"))

calculated column in import method

exclusion of not max = if('table1'[Maxdate]=('table1'[Date]),'table1'[ID],0) = included in the visual filter 

this two calculated column is working fine in the import method,but i am facing issue in replicating the same in direct method.

thanks for help in advance,it would be great if anyone could help in resolving it or guide how to re-write the same in direct query method

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

For direct query model, there is no data be imported, so when add a calculate column, there is no row context, it is limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions.

Limitations in calculated columns: Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions. Additionally, the DAX scalar functions (such as LEFT()) that are allowed, will be limited to those which can simply be pushed to the underlying source, hence will vary depending upon the exact capabilities of the source. Functions that are not supported will not be listed in autocomplete when authoring the DAX for a calculated column, and would result in an error if used.

https://community.powerbi.com/t5/Desktop/Filter-in-calculated-column-in-direct-query-mode/td-p/332528

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

and you could use calculate measure instead of calculate column

Maxdate =
CALCULATE (
    MAX ( ' Table1'[CommonDate] ),
    ALLEXCEPT('table1','table1'[ID]),
    FILTER (
        ALLSELECTED ( 'table1'[created calulated category column] ),
        'table1'[created calulated category column] = "category1"
            || 'table1'[created calulated category column] = "category2"
            || 'table1'[created calulated category column] = "category3"
    )
)

exclusion of not max = if([Maxdate]=SELECTEDVALUE('table1'[Date]),SELECTEDVALUE('table1'[ID]),0)

or

exclusion of not max = if([Maxdate]=SELECTEDVALUE('table1'[Date]),SUM('table1'[ID]),0)

Best Regards,

Lin