Updating Data and Relationships

[W2SANC]

Hello, 

I have a dataset that is from a certain timeframe (let's call it Q1/Quarter 1). I have my relationships and dashboard all set. How the data is set up in my power Query is that I appended all of the data together from various submissions of data (each colleague all reports the same data but they send in their own data separately). So when I receive all of the files from my colleagues, I clean them individually and append them to one another. 

Now that we have collected responses for Q2, I am not sure the best method to update my visuals/dashboard. I thought of appending it to the Q1 data, but, on the model tab, it says there can only be 1 active realtionship at a time, so I don't know how to connect them in the modeling tab. What is the best option?

Another consideration: We only want Q1, Q2,, Q3 and Q$ (1 year of data) in the dashboard at a time). I have to consider how I do this now, so that in the future, I can remove the Q1 data, and replace with the next (Q5) round of data, (keeping Q2-Q5) so we have only 1 year's worth of data in the visuals at all times. 

What is the best method to ensure I am set up for success in the future and to successfully update my dashboard with this new data?

Thanks in a advance! 

[Idrissshatila · Accepted solution]

Hello @W2SANC ,

The best option is to have all these data of Q1, Q2, etc... in one table and this would be your fact table and the other tables would be the dimension tables. thus it will be a star schema model.

learn more about star schema https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[W2SANC]

If I have a column of what Quarter each sale is from, is it possible to append data as all of the columns are the same, and then filter for specific Quarters? Is that a solution as well? 

[bolfri · Accepted solution]

Yes, it's. Better than that - you can manage cleaning the imput data directly in Power Query M editor eg. each person who is sending you data can put thier files into folder (eg on OneDrive/Sharepoint) and Power BI can clean them up and append them automaticcly. You can receive a Q number from file name if there is no such column in your data.

Can you send a sample data that you are receiving for Q1/Q2 (sample dataset, even files that you are receiving)?

[Idrissshatila · Accepted solution]

Hello @W2SANC ,

The best option is to have all these data of Q1, Q2, etc... in one table and this would be your fact table and the other tables would be the dimension tables. thus it will be a star schema model.

learn more about star schema https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin