use filter function in data model

New Contributor

how can I get array of filter function from a data model in power pivot?


i want to filter data between two value in data model by filter function and then make pivot table from that data.


7 Replies


Excel built-in functions have no access to data model, they work with data in grid. You may create DAX measure(s) which aggregates filtered data and use it in PivotTable.

You can't do that except you can load your data to power query > use inner join>then load the data into power pivot model and then load it to pivot table

What filter function can do, power query can do..


If data is already in data model it's not necessary to re-build it with Power Query, I guess DAX is enough.

You're absolutely right!
Is it possible to get two date as input from worksheet and filter data in data model?


In general you shall apply filter to measures you use. For such sample


you may load (e.g. by Power Query) Start and End into the data model and use them in Totals measure like

Total:=VAR startDate =
    VALUES ( 'Start'[Start] )
VAR endDate =
    VALUES ( 'End'[End] )
VAR first =
    MAX ( MIN ( Table1[Date] ), startDate )
VAR last =
    MIN ( MAX ( Table1[Date] ), endDate )
        SUM ( Table1[Value] ),
        Table1[Date] >= first
            && Table1[Date] <= last


Yes, Sergei Baklan has already answered your question.. check his response

You can use Xlookup to do same work as filter
Filter(Data, (Data[Year]>=1992)*(Data[year]<=2009)) assuming start year is 1992 and end date is 2009
Equivalent of using Xlookup is below
=Xlookup(lookup value is 1992, lookup Array is date column of A1:A10, ReturnArray is still date Column A2:A10) : Xlookup ( lookup value is 2009 as second input, A2:A10, return the very lastColumn on your table)

Or you can use Offset function with Xmatch if you want..

Performance wise, Filter is the Best
But Mr Sergei Baklan has given you the answer already..