Using different tables with measure table and calender table

[Anonymous]

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.

[rajendraongole1]

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]

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