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"...
Need Help
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?

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

      Hi jayeckles,

       

      We should use this measure in PBI.

       

      Best Regards!

      Dale

      • jayeckles's avatar
        jayeckles
        Advocate III
        9 years ago

        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.