locked
Creating a Summary report using relationship between date table and two fact tables. RRS feed

  • Question

  • Hi,

    I have below tables in my power pivot.

    Fact 1 & Fact 2 - connected directly to Mainframes - Data is from the same table broken up due to size of the data.

    Date Table - Relation ship between both the fact tables.

    How do i create a summary pivot to get the number of tasks that have been completed in each month.

    Month   Count

    July

    August

    September

    October.

    Any suggestions.

    Regards,

    Renato.

    Monday, October 19, 2015 7:15 PM

Answers

  • Hi Renato,

    According to your description, you need to create a summary report using relationship between date table and two fact tables, right?

    I have tested it on my local environment, here are the sample steps for you reference.

    1. Load two fact tables and date table in two PowerPivot data model.
    2. Create calculated column using the DAX expression below on both fact tables.
      =CALCULATE(COUNT(Fact1[TaskID]),ALLEXCEPT(Fact1,Fact1[Month]))
      =CALCULATE(COUNT(Fact2[TaskID]),ALLEXCEPT(Fact2,Fact2[Month]))
    3. Create calculated column using the DAX expression below on Date table.
      =LOOKUPVALUE(Fact1[CountTask],Fact1[Month],'Date'[Month])+LOOKUPVALUE(Fact2[CountTask],Fact2[Month],'Date'[Month])
    4. Create Pivot chart using the Date table.


    If this is not what you want, please elaborate your requirement, so that we can make further analysis.

    Regards,



    Charlie Liao
    TechNet Community Support


    • Edited by Charlie Liao Wednesday, November 25, 2015 7:36 AM
    • Proposed as answer by Charlie Liao Wednesday, December 2, 2015 8:38 AM
    • Marked as answer by Charlie Liao Thursday, December 3, 2015 8:38 AM
    Wednesday, November 25, 2015 7:32 AM

All replies

  • Hi Renato, How did you connect and import the two tables to the Data Model? I recommend using Power Query for the import and then append the two queries and load to Data Model. Then you have a simpler schema of a single fact table, and you probably know how to create your report from a single fact table :)
    Friday, November 20, 2015 9:46 AM
  • I'd echo the advice Gil gave to combine both fact tables into a single table if they share the same structure.

    What makes you think that you need to split them up due to size? Power Pivot has no defined limit of how many rows or columns can be in a table. In terms of data compression, a single taller table should have a lower memory footprint than two tables with the same schema stored separately.

    GNet Group BI Consultant

    Friday, November 20, 2015 3:22 PM
  • Hi Renato,

    According to your description, you need to create a summary report using relationship between date table and two fact tables, right?

    I have tested it on my local environment, here are the sample steps for you reference.

    1. Load two fact tables and date table in two PowerPivot data model.
    2. Create calculated column using the DAX expression below on both fact tables.
      =CALCULATE(COUNT(Fact1[TaskID]),ALLEXCEPT(Fact1,Fact1[Month]))
      =CALCULATE(COUNT(Fact2[TaskID]),ALLEXCEPT(Fact2,Fact2[Month]))
    3. Create calculated column using the DAX expression below on Date table.
      =LOOKUPVALUE(Fact1[CountTask],Fact1[Month],'Date'[Month])+LOOKUPVALUE(Fact2[CountTask],Fact2[Month],'Date'[Month])
    4. Create Pivot chart using the Date table.


    If this is not what you want, please elaborate your requirement, so that we can make further analysis.

    Regards,



    Charlie Liao
    TechNet Community Support


    • Edited by Charlie Liao Wednesday, November 25, 2015 7:36 AM
    • Proposed as answer by Charlie Liao Wednesday, December 2, 2015 8:38 AM
    • Marked as answer by Charlie Liao Thursday, December 3, 2015 8:38 AM
    Wednesday, November 25, 2015 7:32 AM