tabular model - filtering records based on date slicer

[jayeckles]

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.

Example:

Award

id	ValidFrom	ValidTo
1	2015-01-01	2016-01-01
2	2015-01-05	2016-01-01
1	2015-02-01	2015-10-01

DateParam

Date

2015-01-02

2015-12-31

2016-03-01

NB: In my tabular model there is no relationship between Award and DateParam.  

Desired outcome:

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:

=if(HASONEVALUE(DateDim[date]),if(and( Values(DateDim[date]) >= Award_all[ValidFrom], Values(DateDim[date]) <= Award_all[ValidTo]),1,0),1)

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?

[v-jiascu-msft]

Hi @jayeckles,

We could try a measure to tag them. If we don't want them to display, we can filter them. 

IfIn =
IF (
    HASONEVALUE ( DateParam[Date] ),
    IF (
        MIN ( DateParam[Date] ) >= MIN ( Award[ValidFrom] )
            && MIN ( DateParam[Date] ) <= MIN ( Award[ValidTo] ),
        1,
        0
    ),
    9
)

Why can we use "MIN" (or MAX) here? One row is a single record in the table visual in this scenario.

Best Regards!

Dale

[jayeckles]

I'll give this a try when I get a chance.  Do you recommend creating the measure in SSAS, in PBI, or do you think it matters?

[v-jiascu-msft]

Hi @jayeckles,

We should use this measure in PBI.

Best Regards!

Dale

[jayeckles]

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.