Forum Discussion

yuhkao's avatar
yuhkao
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Date relationships with multiple tables

Hi Expert

 

I have two table need to calculate some meausre with relationship linkage. i already created date table with multiple format of date

dont know how to manage relati

 

 

Table 1 - every single event info on each row

Event DateProductClassified
5/1/2020P1c1

4/3/2020

P2c2

3/20/2020

P1c3

...

 

Table2 - volume with monthly basis

YearMonthVolumeProducts
2020013000P1
2020011000P2
202001500P3
2020022000P1
2020021200P2
2020021500P3

 

 

i would like to calculated and make a chart of count of classified on each products with month of year period like below.

 

 P1P1P1P2P2P2P3P3P3
202001sum of c1sum of c2sum of c3sum of c1sum of c2sum of c3sum of c1sum of c2sum of c3
sum of c1sum of c2sum of c3sum of c1sum of c2sum of c3sum of c1sum of c2sum of c3 
202003sum of c1sum of c2sum of c3sum of c1sum of c2sum of c3sum of c1sum of c2sum of c3
202004sum of c1sum of c2sum of c3sum of c1sum of c2sum of c3sum of c1sum of c2sum of c3
Need Help

5 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    6 years ago

    Hi yuhkao ,

     

    Just wanted to check if you can provide a screenshot around what relationships you have created with these 2 tables and Date table. 

    Also what does this "SUM of C1" represent in your data.

    please provide details on your query.

     

    Thanks,

    Pragati

    • yuhkao's avatar
      yuhkao
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      sorry for mistake 

       

      table 3 should be like this.

       

       202001202002202003202004202005 
      P1c1/volumec1/volumec1/volumec1/volumec1/volume 
       c2/volumec2/volumec2/volumec2/volumec2/volume 
       c3/volumec3/volumec3/volumec3/volumec3/volume 
      P2c1/volumec1/volumec1/volumec1/volumec1/volume 
       c2/volumec2/volumec2/volumec2/volumec2/volume 
       c3/volumec3/volumec3/volumec3/volumec3/volume 
      P3c1/volumec1/volumec1/volumec1/volumec1/volume 
       c2/volumec2/volumec2/volumec2/volumec2/volume 
       c3/volumec3/volumec3/volumec3/volumec3/volume 
             
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    yuhkao , your output is not very clear

    In second table you can have date

    Date = date(left([YearMonth],2),right([YearMonth],2),1)

     

    And now you can join both tables with date and common product dimension and create visual based on that

    • yuhkao's avatar
      yuhkao
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      amitchandak yes my problem is how to make correct relationship with 2 tables to make result like table 3 for visual chart. 

      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User
        6 years ago

        Hi yuhkao ,

         

        Just thinking if you need a DATE table or not.

        You can create a bridge table containing unique values for - P1, P2, P3, etc.

        Use this bridge table to create relationships between you 2 other tables.

         

        Then you can directly move the required fileds to a table or matrix visual to achive the desired result.

         

        If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

         

        Thanks,

        Pragati