Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Using different tables with measure table and calender table

Hi everyone,

I have a table of A with date format:

and a table of B with same date format:

and i created a new table calendar same date format:

# and then i link it together as: 

a - calendar : many to many

b - calendar: many to many

a-b many to 1

# Then i created a measure table using :

- countrows from a

- countrows from b

- rate = a/b

=> But the problem is when i use measure with date calendar table it shows the same number for any day, it only shows right number when i drag the exact table data date come from (a or b but not the same time)

So how can i use different table with calendar table and measure table ? what have i done wrongly with table relationship?

 

Thank you guys so so much.

 

Need Help

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous - Many-to-many relationship between tables and your calendar table,is always be unexpected behaviour when you are performing aggretating the data.

    you can create a below relationships between tables as follows:

    Create a one-to-many relationship from the calendar table to the A table with single direction.Create a one-to-many relationship from the calendar table to the B table with single direction.

    A table to B table relationship is not required as you already created and using the calendar table correctly in between.

    Now create a measure for countrows for a and b as follows and find the rate measure by using divide both.

    CountRows_A = COUNTROWS('A')

    CountRows_B = COUNTROWS('B')

    Rate measure = DIVIDE([CountRows_A], [CountRows_B], 0)

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Rejen rajendraongole1 ,

       

      Thank you so much for your suggest. however when I try Calendar - table A : 1:many (single) - active relationship and Carlendar - table B: 1:many (single) (Cant not click on active relationship box because they are not allowed so i just unticked ), then the chart just work only for table A, and table B it is counted total for any date so or each month/week is same as total

       

      Please help.

      Many thanks