Forum Discussion

John_C_2005's avatar
John_C_2005
Copper Contributor
Sep 01, 2021
Solved

Power Pivot help needed - managing m2m relationship

Hi Forum 

PC/windows 10 - Excel 19 ( Professional Plus 2019)

Can someone assist me please as I seem to be going around in circles ? I am relatively new to Data Tables and Power Pivots.

 

I am trying to combine 3 data tables together ( GRC, RIBB & TREAS) into one Pivot table . There is a common column (GRC Product Code) but there is a many to many relationship between RIBB & TREAS.

 

I watched a YouTube video which helped and I set up a bridge table called Unique to manage this relationship. However I cannot get the pivot table correct ( GRC to RIBB & GRC to TREAS) are correct but cannot get all three products alligned. I almost dont care what the Pivot Values are,  the real reason behind this exercise is connecting the tables to show all the different 'fruit' names and codes from the different sources etc.

 

Any help you can give would be much appreciated.

 

Regards

BI & Data Analysis
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 01, 2021

    John_C_2005 

    I'm not sure if GRC table is with unique code or not. If yes, you may use relationships as

    combining GRC and RIBB tables.

    TREAS you may add using virtual relationships using aggregation like

    TR:=CALCULATE( COUNTROWS(TREAS),
    TREATAS( VALUES( RIBB[GRC Product Code] ),
             TREAS[GRC Product Code] )
)

or

TRF:=CALCULATE( SUM(TREAS[Treasury UID]),
     TREATAS( VALUES( RIBB[GRC Product Code] ),
              TREAS[GRC Product Code] )
)

    , whatever. In any case you need something in Values of PivotTable, otherwise it always will be Full Outer relationship. Don't know what Treas Filter shall mean, in above is TRF instead.

     

    Finally PivotTable is

    If that's what you expect you may remove Unique table at all.

     

    Please check in attached file.

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 01, 2021

    John_C_2005 

    I'm not sure if GRC table is with unique code or not. If yes, you may use relationships as

    combining GRC and RIBB tables.

    TREAS you may add using virtual relationships using aggregation like

    TR:=CALCULATE( COUNTROWS(TREAS),
    TREATAS( VALUES( RIBB[GRC Product Code] ),
             TREAS[GRC Product Code] )
)

or

TRF:=CALCULATE( SUM(TREAS[Treasury UID]),
     TREATAS( VALUES( RIBB[GRC Product Code] ),
              TREAS[GRC Product Code] )
)

    , whatever. In any case you need something in Values of PivotTable, otherwise it always will be Full Outer relationship. Don't know what Treas Filter shall mean, in above is TRF instead.

     

    Finally PivotTable is

    If that's what you expect you may remove Unique table at all.

     

    Please check in attached file.

    • ritendersingh2's avatar
      ritendersingh2
      Copper Contributor
      Nov 28, 2021

      SergeiBaklan  

      @Sergei Baklan , Sir I am also facing a similar but bit complicated issue (for me atleast) 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 Status(Column AB) is Selected, and then joining data for all who has joined.

       

      I want to create a PivotTable that give me the journey of a candidate -

      -When he was contacted- From Walkin - If he was contacted three-time all the dates

      -Date of Shortlisting (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?

       

    • John_C_2005's avatar
      John_C_2005
      Copper Contributor
      Sep 02, 2021
      Thanks Sergei. Works great. You are a star. Much appreciated.

Resources