cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jayeckles
Advocate II
Advocate II

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.

 

Example:

 

Award

idValidFromValidTo
12015-01-012016-01-01
22015-01-052016-01-01
12015-02-012015-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?

 

4 REPLIES 4
v-jiascu-msft
Microsoft
Microsoft

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.tabular model - filtering records based on date slicer.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

Hi @jayeckles,

 

We should use this measure in PBI.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors