locked
DAX to filter pivottable data from data model RRS feed

  • Question

  • Hi,
    I know how to connect Excel tables (listobject, tableobject) to the Data model.
    I also know how to use Dax queries to filter and shape that Excel table.
    Now I want to do something alike with a pivottable which is connected to the Data model. I want to filter the data coming from the Data model BEFORE it reaches the pivottable/pivotcache. Any ideas if this is possible?
    Of cause I can filter the data using a (page)filter in the user interface but that is not what I am looking for.
    Thx.



    Frans Bus pixcels.nl


    • Edited by Frans Bus Wednesday, December 16, 2015 6:45 PM
    Wednesday, December 16, 2015 6:41 PM

Answers

  • You can't do that in PowerPivot. You can only filter the data that is loaded into your model or you can put a page filter on the pivot table.

    What you are describing sounds a bit like a security role. You can use those to filter the data specific users/groups can see in a SQL Server Analysis Server Tabular model (which uses the same engine as Power Pivot). But this is not available in Power Pivot because if someone has access to the .xlsx file they can edit the model so an end user could just change their own security settings to bypass something like this.


    http://darren.gosbell.com - please mark correct answers

    Thursday, December 17, 2015 1:58 AM

All replies

  • You can't do that in PowerPivot. You can only filter the data that is loaded into your model or you can put a page filter on the pivot table.

    What you are describing sounds a bit like a security role. You can use those to filter the data specific users/groups can see in a SQL Server Analysis Server Tabular model (which uses the same engine as Power Pivot). But this is not available in Power Pivot because if someone has access to the .xlsx file they can edit the model so an end user could just change their own security settings to bypass something like this.


    http://darren.gosbell.com - please mark correct answers

    Thursday, December 17, 2015 1:58 AM
  • Thanks Darren, I was afraid this would be the answer...

    I solved it by connecting the pivot to a stored procedure in the database. The app is becoming a kind of hybrid solution now: sometimes I connect a pivot to the data model, sometimes directly to the source data. Same for tables: sometimes I use a TableObject (connected to the data model), sometimes I load data into a Listobject directly from the database (Ado or Querytable). Would prefer to load all data from the data model, not needing the database.

    Thx!


    Frans Bus pixcels.nl

    Tuesday, December 22, 2015 10:47 AM