tabular model - filtering records based on date slicer
I am connecting a Power BI report to a tabular model. The model has a table (call it "Awards") that includes both current and historical data. The records have fields with "ValidFrom" and "ValidTo" dates. There is a parameter table (call it "DateParam") in the model that includes dates that can be selected. I want a table visual in Power BI to display only those records from Awards where the date selected in a DateParam slicer falls between the ValidFrom and ValidTo dates.
NB: In my tabular model there is no relationship between Award and DateParam.
If I have a slicer for DateParam and select 2015-01-02 as the value, I want a table visual to display the id, ValidFrom, and ValidTo fields for only records 1 and 2 (record 3 should not be displayed because 2015-01-02 is not between record 3's ValidFrom and ValidTo).
Tried so far:
1. I have been able to create measures that count the records based on the DateParam slicer value, but I don't want a summarization of the records, I want a display of the records in the table visual.
2. I tried adding a calculated column to the tabular model called DisplayFilter with this DAX expression:
Unfortunately, selecting a value from the DateParam slicer doesn't seem to change the initial value of DisplayFilter. I have read something suggesting that calculated columns are only calculated at processing time.
Are there other ways to apply the parameter table pattern to get this kind of dynamic filtering I want?
This did end up meeting the requirements specifically as I stated them, which is to say that it allows me to filter a table.
Unfortunatley, the table was just a specific instance of a more general requirement, and that is to be able to filter more or less any visualization based on the date I select.
For example, say I want a card that will count the award records that are displayed in my filtered table. Or, I want the sum of award amounts from the records displayed in the filtered table.
My expectation was that once I had a filter flag (your IfIn measure), I could then use that as a page level filter. Alas, I cannot (perhaps because a measure can't be used as a page level filter).
I'm really looking for a general solution to how to select an "as of" date for a report that is driven by a tabular model with historical data. I am open to other ways of designing the tabular model itself.