When I create a join between the tables, the rows get Duplicate rows. Due to this, when I use the sum function on a field, the data in the visual is shown as wrong, It takes the sum of all duplicated rows.
For Eg I have “User_table” " which is basic data like ‘age’, ‘name’, and ‘email’, if there is another table like Food which shows everyday fat, calories, protein data of the user Sleep which shows everyday max, min, resting heart rate of the user, which are joined If there are N days data. When I join this table it creates N rows of data. Here No problem here, I was able to handle using the filter. [rows are joined on Userid and Date for intraday data]
Main problem If the User has multiple Health conditions, We created a join from the user to the health condition table, for each condition the rows get duplicated.
Due to this multiple rows sum does not work correctly, If I want to show the sum of protein a user consumed it Shows a Higher Multiple of total protein due to duplicated rows. In Power Bi due to relationship, this error does not occur.
So please clarify, can Quicksight handle complex data with multiple joins, how to get a correct sum of fields? I have added the data model in the below image.
Do you need all the data joined in the same DataSet? I would recommend to have one DataSet based on the needs of the specific Visuals, if you need all the data together you could deal with the duplicated rows by using averages instead of sums with a proper grouping.
I have not tested this but those are the options I can think of.
Please guide me here, I am new, to Quicksight how should I structure the data here, I have used Power BI, there I used to create relationships so duplicate rows did not get created. In Quicksight if I used a different dataset how would I do row-level security for each dataset, as the column I am using for RLS is in the Company table, and when I join it with the User table it creates Duplicates. Kindly guide us on how we should structure all the data and, how to drill down and filter if multiple data sets are used.
Not sure how PowerBI works when creating relations and why it did not show duplicates, in QuickSight, they are joins between the tables, i you have a relation then your resulting dataset will have the data depending on the kind of join, inner, left, right, outter.
Here you can find more information on joining data in QuickSight
To perfrom RLS in different datasets, you need the column you will use for RLS in each of the datasets and have that applied in each dataset.
It is hard to advise any further without looking at the data model. It is possible that the data model you have might not be useful for the visualizations you want and you might need to do some more previous data processing than just joining tables.
Hi @AjayV,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.
