Excel Tables and Relationship

[ritendersingh2]

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

[#2]

@ritendersingh2 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:

[#3]

@ritendersingh2 

In addition, I'd recommend to check 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]
        )
    )
)

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

[#4]

@Sergei Baklan , 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 .

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

 How can I do that with or without Pivot Table

[#5]

@ritendersingh2 

Can't say I understood in which form you'd like to have the result and what exactly shall be included. 

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.