locked
PowerPivot inner join? RRS feed

  • Question

  • Hallo,

    I have a question about PowerPivot relations. Default relations behave like SQL outer joins.
    Is it possible to define a relation as an inner join?

    I would like to use a relation as a filter to define several PoverPivot reports using the same data source (SQLServer view)

    Regards Dirk

    Tuesday, March 19, 2013 8:01 AM

Answers

  • Guys, I created a quick example that I saved here which uses Amit's data.

    Its not particularly clever but does produce the required results, basically I created an ID dimension table to which each fact table is related (as the many) and a measure to check whether each fact table in the current context had one row or more. Where one more row was present in each fact table for that ID, the answer then returns the measure which adds the values of all three tables:

    if(COUNTROWS('Table A')>0&&COUNTROWS('Table B')>0&&COUNTROWS('Table C')>0,"Y",BLANK())

    I make no claim to be a DBA or any kind of SQL authority but what I do know is that PowerPivot functions best when with combined with SQL queries that handle on import the things that PowerPivot isn't so good at e.g. complex relationships, calculated columns etc.

    In other words if this was my problem I'd be looking at dealing with this on import!

    Hope this helps

    Jacob





    • Edited by Jacob Barnett Thursday, March 21, 2013 9:53 AM
    • Proposed as answer by Amit Srivastava Friday, March 22, 2013 9:33 AM
    • Marked as answer by Elvis Long Thursday, March 28, 2013 2:32 AM
    Thursday, March 21, 2013 9:50 AM
  • Amit, there is no work around that I know of although if you used a slicer instead of a filter that would at least differentiate those items with data from those with none.

    Jacob


    • Edited by Jacob Barnett Thursday, March 21, 2013 11:40 AM
    • Marked as answer by Elvis Long Thursday, March 28, 2013 2:32 AM
    Thursday, March 21, 2013 11:40 AM

All replies

  • Dirk, 

    There is no alternative to the default relationship in PowerPivot per se.

    Perhaps if you give a fuller explanation of what you are trying to achieve maybe someone will have an answer. There are a number of powerful DAX techniques that can be leveraged to overcome the limitations of PowerPivot's relationships.

    Jacob

    Wednesday, March 20, 2013 4:17 AM
  • Hi Jacob,

    I do have same problem that i am not able to implement inner join in Three table instead it is Outer JOin

    My scenario is :

    I have three table A,B,C, acting as datasources from Power Pivot, i just import data from SQL tables to Power pivot

    Data Structre Table A

    ID  Value

    1    10

    2    20

    Data Structre Table B

    ID Value

    2    20

    3    30

    Data Structre Table C

    ID Value

    2    20

    4    90

    Now, i want to have data which is common to all three report based on ID mapping in power pivot as requirement of report is to show only common data to all the table, but when i created relationship under Power Pivot in diagram view, relation is full outer join not a inner join, which give each and every record to me like as below

    ID Value

    1   20

    2   60

    3  30

    4  90

    but i want data common to all table i.e record with id =2 , data will be like as below

    ID Value

    2   60

    In Short , i want data to filter on the basis of Inner join instead outer join, please suggest any other way around if inner join is not feassible in Power Pivot.


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Thursday, March 21, 2013 7:22 AM
  • Guys, I created a quick example that I saved here which uses Amit's data.

    Its not particularly clever but does produce the required results, basically I created an ID dimension table to which each fact table is related (as the many) and a measure to check whether each fact table in the current context had one row or more. Where one more row was present in each fact table for that ID, the answer then returns the measure which adds the values of all three tables:

    if(COUNTROWS('Table A')>0&&COUNTROWS('Table B')>0&&COUNTROWS('Table C')>0,"Y",BLANK())

    I make no claim to be a DBA or any kind of SQL authority but what I do know is that PowerPivot functions best when with combined with SQL queries that handle on import the things that PowerPivot isn't so good at e.g. complex relationships, calculated columns etc.

    In other words if this was my problem I'd be looking at dealing with this on import!

    Hope this helps

    Jacob





    • Edited by Jacob Barnett Thursday, March 21, 2013 9:53 AM
    • Proposed as answer by Amit Srivastava Friday, March 22, 2013 9:33 AM
    • Marked as answer by Elvis Long Thursday, March 28, 2013 2:32 AM
    Thursday, March 21, 2013 9:50 AM
  • Hi Jacob,

    That works fine but i have came across one issue over here..........

    Though data displayed is only for the ID=2, but when i select DropDown of ID against ID column, it shows all the ID like 1,2 ,3,4.though when i select any id except ID=2, it not showing any data, thats perfect, but only the showstopper is Listing of all ID (1,2,3,4) under ID column filter  that resulting into confusion to the end user, please let me know is there is any workaround to get rid of all id there in dropdown and user can see only those Id's in dropdown those having data.


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/


    Thursday, March 21, 2013 11:28 AM
  • Amit, there is no work around that I know of although if you used a slicer instead of a filter that would at least differentiate those items with data from those with none.

    Jacob


    • Edited by Jacob Barnett Thursday, March 21, 2013 11:40 AM
    • Marked as answer by Elvis Long Thursday, March 28, 2013 2:32 AM
    Thursday, March 21, 2013 11:40 AM
  •  Thanks Jacob, it seems bottleneck in the Report development with the help of power pivot excel :(

    Any ways, thanks for your valuable guidelines :)


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Friday, March 22, 2013 9:33 AM