Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

Filter table by latest value in another table (DirectQuery)

So I have these two tables in DirectQuery mode:



ID            Date1



Date2         Value


I need to make a sum of all the Values in Table2, for which the Date2 is after the latest Date1 in Table1. 


I created a Measure returning the latest Date1 using MAXX. Then I tried to create a new column in Table2 with two values - Latest and Other, using IF (Table2[Date1]>Measure,"isLatest","Other") so I can use this column in page/visual filters. But I get the error MAXX is not allowed in DirectQuery, and I have to use DirectQuery.


Can you please share your thoughts?





Why don't you use a measure instead of a calculated column ?


Try this:


TotalSalesAfterDate1= VAR MxDate1 = Max(Table1[Date1]) RETURN
Calculate(Sum(Table2[Value]) , Table2[Date2] > MxDate1)


Please note that you can allow unrestricted measures in DirectQuery mode if you go to File > Options and then Settings > Options and settings > DirectQuery.

It works! Thanks!


But now I have another problem. Actually the tables have many more columns and I have several visuals based on those columns. How can I filter those visuals to display values only after the latest Date1?


Adding a calculated column based on the measure you suggested is not possible because it's not accepted in DirectQuery.



Thanks again

Hi @valivali,

If your resource data is not very large, you'd better upload the data using import mode rather than Direct Query. There are some limitations using DirectQuery in Power BI Desktop.

You get the latest date, you can set visual level filter data is greater or equal to the latest data if you want to filter many visuals.

Best Regards,

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors