Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kell
Helper I
Helper I

Using different tables with measure table and calender table

Hi everyone,

I have a table of A with date format:

kell_0-1718536999027.png

and a table of B with same date format:

kell_1-1718537041455.png

and i created a new table calendar same date format:

kell_2-1718537101367.png

# 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.

 

2 REPLIES 2
rajendraongole1
Community Champion
Community Champion

Hi @kell - 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!!

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors