How to handling multiple one to many relations

I am joining 3 tables A (plants)-> B (Employees) and A (Plants)->C. (Products) this is resulting in 1 huge resultant table having columns from all the 3 tables. This is leading to a cross join and the number of records are increasing resulting in wrong calculations eg sum. Is there any way to solve this where we have relations in tableau or Power Bi so that the 3 tables are linked but one resultant table is not generated.

1 Like

Hey bvinodk!

Getting more records will be based on the join. Having more columns isn’t going to change the sum calculations.

A couple of things to try.

  • Using different types of joins (inner, left, right).

  • Creating a different dataset and joining the A table to that dataset: Create a different dataset that joins B->C and then A → (B->C)

In regards to the addition of columns, you can exclude those columns from the dataset. Again, it won’t solve the issue of more records, but it can clean up your dataset when you are using it.

image

Let me know if that helps!

Hi max, the problem I’m facing is different, i know that we can exclude the column fields, which reduces the no of columns. Due to difference in the granularity of the tables when I’m joining multiple tables, I’m getting too huge additional rows. When I need to get the sum of the profit of a product for a particular year, I’m taking the avg function instead of sum becz the granularity of the profit is at year level but due to joining of tables it resulted in multiple rows of profit for a product for an year. If suppose when i get data at day level it will be a big issue when i join tables at different granularity levels. Granularity may be different in dates(year,month,quarter & day) or in products( category, sub category & charge headers). How to handle such kind of scenarios. One of the main issue here is we are unable to use a table if there is no relation b/w that table and other tables…

Take for example, I need to interconnect 3 tables in quicksight. So I have a Table called

  • production ( production_id, plant details , date , units produced , consumption ……) which has a factory production details.
  • Production_Products has all products that were produced in that production( production_id, product_id, product_name etc)
  • Production_employee has all the employees who were working when a production took place ( production_id, emp_id, emp_name , time in , time out etc )
    Production table is joined up production products through production_id(1 to many relation) and production table is joined up production employee through production_id (1 to many relation )

So what is happening in quicksight is on interconnecting these 3 tables through joins it is creating 1 big table having columns coming in from all the 3 tables and a cross join is formed… since 1 production_id from the production table can have multiple products and it can also have multiple employees… so if I have to take a sum of any of the measures coming from this big combined table, the sum is getting increased drastically… is there any way to interconnect these tables in quicksight such that 1 big table is not formed but I can still somehow connect these 3 tables and use them

I don’t think there is a way of doing that in Quicksight.
You can use some hacks to do this, and create a calculated field that divides your measure by the number of rows, so that the total is correct.
Something like that : nb_units / countOver(nb_units, [product_id], PRE_AGG)
or you can use different datasets if you don’t need to use them in the same visual

Hi,

Thanks for the details regarding the links to your tables. Would you be able to provide a dummy dataset and add a few images on the calculations you are looking at ?

Regards,
Koushik

Hi @bvinodk,

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 7 days, we will archive the question.