Forum Discussion

Renco's avatar
Renco
Advocate II
8 years ago
Solved

Filter table dynamicaly on dates

Hello community,   I'm trying to get a table (or measure) which is filtered through dates. What I want to achieve is to filter the table on "begindatum" (which is starting date in English) and I o...
Show and Tell
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago
    Renco wrote:

    With an OData connection or any other 'import' connection this works like a charm. However with a DirectQuery connection this solution is not possible.

     

    So if someone knows another way to achieve this goal, suitable for a DirectQuery connection, I'm looking forward to hearing it.

     

    Thanks in advance.

    Renco

    In DirectQuery mode, say if the data source is SQL Server, you can create the columns in SQL instead of in Power BI desktop. Then you can still apply filter to the true/false column.

Renco's avatar
Renco
Advocate II
8 years ago

This works:

 

 

New Column in modelling tab:

 

Datum = CALCULATE(LASTDATE(Klantenprijsgroep_Sheet[Begindatum]);ALLEXCEPT(Klantenprijsgroep_Sheet;Klantenprijsgroep_Sheet[Artikelnr.]);Klantenprijsgroep_Sheet[Begindatum]<=TODAY())

 

With another column:

 

Rapportage = IF(Klantenprijsgroep_Sheet[Begindatum]=Klantenprijsgroep_Sheet[Datum];true();false())

 

And filtering in the visual itself on 'true'.

 

Perhaps there is a better way than this but at least it's a solution

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    8 years ago

    The solution is elegant, for me, I'll take the same solution. 

    • Renco's avatar
      Renco
      Advocate II
      8 years ago

      With an OData connection or any other 'import' connection this works like a charm. However with a DirectQuery connection this solution is not possible.

       

      So if someone knows another way to achieve this goal, suitable for a DirectQuery connection, I'm looking forward to hearing it.

       

      Thanks in advance.

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        8 years ago
        Renco wrote:

        With an OData connection or any other 'import' connection this works like a charm. However with a DirectQuery connection this solution is not possible.

         

        So if someone knows another way to achieve this goal, suitable for a DirectQuery connection, I'm looking forward to hearing it.

         

        Thanks in advance.

        Renco

        In DirectQuery mode, say if the data source is SQL Server, you can create the columns in SQL instead of in Power BI desktop. Then you can still apply filter to the true/false column.