Excel Tables and Relationship

Question

Hello - I am using excel 2016 standard version, which doesn't have a power pivot, how can I create a pivot table from two tables having duplicate values in each table, I cannot create a relationship as that would require unique values in at least one table

riny_van_eekelen · Answer

ritendersingh2&nbsp;Create a third table containing unique names and create relationships from each of the transaction tables to the name table.The relationships could then look something like this:&nbsp;

sergeibaklan · Answer

ritendersingh2&nbsp;
In addition, I'd recommend to check&nbsp;Physical and Virtual Relationships in DAX - SQLBI
As a sample, for such tables

you may build virtual relationships using in your measures
sm TREATAS:=CALCULATE (
    SUM ( Table2[Y] ),
    TREATAS (
        VALUES ( Table1[A] ),
        Table2[B]
    )
)

//////////////////////

sm INTERSECT:=CALCULATE (
    SUM ( Table2[Y] ),
    INTERSECT (
        ALL ( Table2[B] ),
        VALUES ( Table1[A] )
    )
)

//////////////////////

sm CONTAINS:=CALCULATE (
    SUM ( Table2[Y] ),
    FILTER (
        ALL ( Table2[B] ),
        CONTAINS (
            VALUES ( Table1[A] ),
            Table1[A], Table2[B]
        )
    )
)

&nbsp;
First one with TREATAS is most preferable but doesn't work in 2016, other two shall work.

ritendersingh2 · Answer

SergeiBaklan&nbsp;, Sir I am attaching the excel file with my data, I have 3 tabs here, Walkin is my daily calling data with their status and all, Shorlisted is where my Final Satus(Column AB) is Selected ,nd t hen joining data for all who has joined .&nbsp;I want to create a PivotTable which give me the journey of a candidate --When he was contacted- From Walkin - If he was contacted three-time all the dates-Date of Shortlising (Column X -from Walkin), this can also be multiple times- Date of Final Interview (Column AA- from Walkin)-Date of Joining - from Joining table&nbsp;How can I do that with or without Pivot Table&nbsp;

sergeibaklan · Answer

ritendersingh2&nbsp;
Can't say I understood in which form you'd like to have the result and what exactly shall be included.&nbsp;
Attached is Power Query variant. From my point of view it's more suitable for this concrete task, if only you don't have something else in mind where data model could give more possibilities.

Forum Discussion

Excel Tables and Relationship

4 Replies

Resources