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...
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

 

 

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