Filter table by latest value in another table (DirectQuery)

[valivali]

So I have these two tables in DirectQuery mode:

Table1

ID            Date1

Table2 

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?

Thanks!

[Datatouille]

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.

[valivali]

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

[v-huizhn-msft]

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,
Angelia