Jan 22 2022 01:21 AM - edited Jan 22 2022 01:22 AM
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.
Jan 22 2022 01:34 AM
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.
Jan 23 2022 03:50 AM
Jan 23 2022 08:55 AM
If data is already in data model it's not necessary to re-build it with Power Query, I guess DAX is enough.
Jan 24 2022 04:46 AM
Jan 24 2022 06:24 AM
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 )
RETURN
CALCULATE (
SUM ( Table1[Value] ),
Table1[Date] >= first
&& Table1[Date] <= last
)
Jan 24 2022 07:24 AM