Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Filter active records at a specific date

 Hello!   So the problem is: I have a classic dataset with datewarehouse historization; each record has a validFrom, validTo and an isActive flag. I want to see the records in a table, that was ac...
rajulshah's avatar
rajulshah
Icon for Resident Rockstar rankResident Rockstar
8 years ago

Hello Anonymous,

 

Using the following DAX function, we can achieve what you want:

IsActive = IF(FIRSTDATE(ALLSELECTED('DTdates'[Date]))<=FIRSTDATE(Table1[ValidFrom]) && LASTDATE(ALLSELECTED('DTdates'[Date]))>=LASTDATE(Table1[ValidTo]) ,1,0)

 

Here is the required result:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Let me know if I am missing on anything.

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

rajulshah

 

Hello Rayulshah!

 

I'm really appreciate your answer! It seems, it would be perfect solution, however FIRSTDATE and LASTDATE functions are not supported in Direct Query mode.

Is there any possible workaround for this kind of problem?

 

Thanks,

Matt

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous,

    Do you connect to SQL Server using Direct Query mode? Could you please post the error message?

    Regards,
    Lydia

  • rajulshah's avatar
    rajulshah
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Hello Anonymous,

     

    Can you please share which datasource are you using for DirectQuery?

     

    If I may be of some help.

  • rajulshah's avatar
    rajulshah
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Hello Anonymous,

     

    Can you please try the following DAX formula and see if it works?

     

    IsActive = IF(FIRSTNONBLANK(ALLSELECTED('DTdates'[Date]),'DTdates'[Date])<=FIRSTNONBLANK(Table1[ValidFrom],Table1[ValidFrom]) && LASTNONBLANK(ALLSELECTED('DTdates'[Date]),'DTdates'[Date])>=LASTNONBLANK(Table1[ValidTo],Table1[ValidTo]) ,1,0)

     

    Let me know if it didn't work.

    • rajulshah's avatar
      rajulshah
      Icon for Resident Rockstar rankResident Rockstar
      8 years ago

      Hello Anonymous,

       

      Let me know if this formula worked or not.

       

      Regards.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        rajulshah

         

        Hello!

        Unfortunatley it does not work.

        I created a sample dataset, under this link. This is used in Azure SQL db, with DirectQuery.

         

         

        Hope this is help.

         

        Thanks,

        Matt