PowerBi DirectQuery Push Down filter on 2 date columns in the same Table used for Slowly Changed Dimension

Question

We are using DirectQuery mode in PowerBI, we want the filter below pushed down at the generated sql level Lets say a Dimension table has ValidSinceStartDate and ValidTillEndDate column If a user wants to filter for lets say 01/01/2020 we want both columns of the same table to be filtered with a between like format

SELECT .. FROM DimensionTable WHERE '01/01/2020' BETWEEN ValidSinceStartDate AND ValidTillEndDate

Can that be done that User enters a date value and PowerBi generates a SQL in directquery mode filtering between two date columns in the same table. We want the data entry to be simple (The user could create two seperate filters on 2 different columns to create the filter providing the right values, that is a little cumbersome though) Also what component would do the data entry?

question from:https://stackoverflow.com/questions/65943870/powerbi-directquery-push-down-filter-on-2-date-columns-in-the-same-table-used-fo

Answer 1

Waitting for answers