locked
Power Pivot Data Model with Linked Table RRS feed

  • Question

  • Can anyone provide me with some suggestions as to how to accomplish the following in Power Pivot?

    I have the following Pivot Table fields. I'm trying to create a data model to allow me to filter all of the projects by the selected Fiscal Year. The Fiscal Year table is a dimension I created to group the data by.

    I'm considering separating Projects into multiple tables such that 1 table is for Year1 information, another table is for Year2 information (includes Year2FiscalYear, Year2BudgetCAP, etc.).

    I think what I need to do is create a measure to allow me to filter the records by fiscal year. Anyone have any ideas?

    Thanks,

    Roland

    Friday, December 19, 2014 8:19 PM

Answers

  • You could not implement my suggestion through the use of calculated columns. You would need to restructure the table to have a greater number of rows, but a lesser number of columns. You cannot add rows with DAX.

    I've created a small sample workbook showing what I mean. You would need to ask for help from whoever provides the data source, or use a tool like Power Query to transform the data to this shape. 

    • Marked as answer by Roland G29 Saturday, December 20, 2014 3:58 AM
    Friday, December 19, 2014 9:31 PM

All replies

  • Just make a [Fiscal Year], a [Project Attribute], and a [Value] column in 'Projects'. Join 'Projects'[Fiscal Year] to 'FiscalYears'[FiscalYear].

    [Project Attribute] will have as values the things you currently store in separate columns: "BudgetCAP", "BudgetEXP"

    [Value] will have for its values the appropriate value currently stored in the separate columns based on the row it is in.

    You can then create a pivot table with simple measures that will react appropriately to slicer selections and you'll never have to worry about what should happen when you get your first project that goes to 3 years, or to 4, which with your current design and suggested design (multiple tables) would require significant rework of the model.

    Friday, December 19, 2014 8:29 PM
  • Hi Greg,

    Can you explain it to me more? If I add a calculated column called "Fiscal Year" to the Projects table, what value does it need to store? I'm not really following. Each attribute Year1FiscalYear, Year2FiscalYear, Year3FiscalYear, etc. can all have fiscal year values. How would that link to FiscalYears[FiscalYear]?

    Thanks in advance for your help.

    -Roland

    Friday, December 19, 2014 8:51 PM
  • You could not implement my suggestion through the use of calculated columns. You would need to restructure the table to have a greater number of rows, but a lesser number of columns. You cannot add rows with DAX.

    I've created a small sample workbook showing what I mean. You would need to ask for help from whoever provides the data source, or use a tool like Power Query to transform the data to this shape. 

    • Marked as answer by Roland G29 Saturday, December 20, 2014 3:58 AM
    Friday, December 19, 2014 9:31 PM
  • That's exactly what I'm trying to figure out. I know this probably would be easier using Power Query but unfortunately I can't use that. I'm trying to use Power Pivot only. Thanks for the sample workbook. It'll help me figure out what the end data model should look like.

    Thanks,

    Roland

    Friday, December 19, 2014 10:55 PM
  • Power Pivot is not a data transformation tool, and you'll often end up creating very brittle, time-consuming solutions when you try to use it as such. Power Pivot is a modeling and semantic layer that sits above the data level.

    May I ask why Power Query isn't an option? It is designed to be the complement to Power Pivot. it is another free COM add-in, like Power Pivot, but it excels at the sort of problem you are looking to solve.

    If you're stuck without Power Query, and Excel is your data source (you've not shared with us where the data is coming from), then that leaves you with doing it by hand, writing some crazy index and lookup formulas in Excel, or writing a rudimentary ETL script in VBA.

    Friday, December 19, 2014 11:12 PM
  • Hi Greg,

    The data is coming from Project Online (OData feed) and SharePoint lists (all within Project Online). I'm creating an Excel Report.

    The reason I can't use Power Query is because refreshing a report that is built using Power Query is not supported via browser. Power BI is needed to do this. On the other hand if I build an excel report using Power Pivot only, the browser refresh functionality works.

    The Year1FiscalYear, Year2FiscalYear, etc. are all enterprise custom fields in Project Online. Therefore I have some control in being able to change the format of the data. I'm looking into what are some options on my side to configure Project Online or SharePoint lists to make building the model simpler...

    Thanks,

    Roland

    Saturday, December 20, 2014 2:31 AM