Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

max function for date within calculated column

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
 
1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-lili6-msft
Community Support
Community Support

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

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.