Forum Discussion
Advocate IISingle table for multiple viz (noob question!)
Hi, first time post and just started using PB right now so apologies for what is likely a dumb question.
I want to build a dashboard from a single 'master' CSV file which has already been generated. Different viz will require different transformations and filters as the CSV format is (necessarily!) complicated.
For example, multiple columns contain the same set of categories, which seems to mean I can't join my 'sorting' table to it for custom category sorting. However, I'd still like those categories sorted in the same order regardless of the column used.
Most viz I will be creating will use a subset of around 4-6 columns relating to the latest financial quarter last year; the next viz might require another 4-6 columns relating to the full financial year this year.
I appreciate I can transform the CSV into a more flat format, however I also need the unflattened format for other parts of the dashboard, eg for year on year analysis.
What is the best practice for data management here - creating a new query for each visualisation I need? Or can I create a transformed version of the master table?
Or is there another way?
I'm mostly concerned about the loading and refresh speed of the end result.
2 Replies
- amitchandak
Super User
Dunners , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Please follow : https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
Also refer: https://www.youtube.com/watch?v=kU2M1LmNvNo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=2
https://www.youtube.com/watch?v=vHuhbvYCiNc&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=3
https://www.youtube.com/watch?v=cJqgphIHXz8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
DunnersHi, sharing an output table may be a little pointless as it's the fact this will be 'multitude' that's causing me the problem.
Here's a link to the PBIX file (via OneDrive) containing a sample of the data:
https://1drv.ms/u/s!AmV_ApArXUyxmFIDrbWgBMPiCF_r?e=f7YTCx
You will see that the same 'metrics' eg. 'profit' and 'segment' are repeated various times, which relate to different time periods. This is necessary as I need to use this for some parts of the dashboard, eg. this month va last month analysis / movement.
Example mentioned before: you will see a different columns for 'Segment' for different time periods eg. SOLM (start of last month) and SOLY (start of last year).
So I am looking for an answer which is what general approach should I take rather than specific table formats.
I have heard about reference tables but I'm not sure whether they applicable here?
