Forum Discussion

jayeckles's avatar
jayeckles
Advocate III
9 years ago

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?

 

Need Help

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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's avatar
      jayeckles
      Advocate III
      9 years ago

      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?