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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!