Forum Discussion

valivali's avatar
valivali
Regular Visitor
9 years ago

Filter table by latest value in another table (DirectQuery)

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...
Need Help
Datatouille's avatar
Datatouille
Solution Sage
9 years ago

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's avatar
    valivali
    Regular Visitor
    9 years ago

    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's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      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