Forum Discussion
Need advice on best method
I need some advice from the guru powers that be. It has been a while since I have been in PBI or access (read as 'rusty') and I want to set this up correctly to limit future issues.
I work for a big company that splits projects/opportunities between districts and joint ventures. My boss wants an interactive histogram/bar chart with particular slicers. Input will be an access database someone else is making and keeping updated.
The Role/Discipline Main Table will show all planned positions/roles with start/finish dates for all projects. This table will have potentially dozens/hundreds of rows per project (many projects) with each row (position) having a start and end date. First pass will be a big chunk of projects being loaded into this, then periodically updated as new individual plans are added on.
The separate % Split Reference Table will be where each project is listed and the split% share by district is identified.
Master Project List would probably be a separate reference table if needed.
Histogram will be a stacked bar chart with slicers on projectID & name, district, discipline, role, time sliders for start/finish period in visual, and ideally a toggle between showing columns by month vs by week. If this last toggle isnt possible, then just by month.
Suspected problems:
- If each row in the main table (showing all roles for all projects) only has start/finish dates, how do I get the histogram to show FTE's (full time equivalent) per period. (assuming 1 FTE per day between the start/finish dates).
- my solution in my example data file is to do an excel query step to add a calculated field =1 FTE and calculate a list for all dates between start/finish and expand these lists. This makes for a very long list for multi year positions as a 1yr position on a single project goes from 1 row to 365 rows. I realize Access may handle this differently than my excel example.
my list formula={ Number.From([START DATE])..Number.From([FINISH DATE]) }
Is there a more eloquent way to do this? Hopefully PBI can extrapolate this visual from the single row of a role with start/finish dates, or worst case in the Access table that someone else is building. I would prefer to limit steps and consolidate in access to reduce processing speed of visual. In my example 42 roles turned into 16k rows when listing out all individual dates. - Where is the best place to connect the two tables (Role/Discipline Main Table, %Split Reference Table)?
If I connect in Access, the output table will have to grow exponentially as more districts get involved. I am hoping I can just build the relationship in PBI so its connection to the two tables can allow the histogram to extrapolate the splits. This should reduce number of rows it has to process.
Ultimately I just want to be able to slice between the districts to show that districts' share. EX: if there is a superintendent role for 1 FTE on a particular day, but District 1's share was only 30% for that project, then changing slicer should change histogram from 1 FTE to 0.3 FTE. - Once PBI visual built, is it possible to be able to toggle between weeks/months view? (each vertical bar period = 1 week vs 1 month)? Some projects are short and some are long and each may merit a different view.
If I am missing anything blatantly obvious please let me know. Like previously mentioned, I am a bit unpracticed and under a time crunch.
Here are screenshots of my sample data i made up in excel until the Access database is ready.
3 Replies
Hi Anonymous,
Current you can't use slicer to filter date based on date range who defined by two data columns. In my opinion, I'd like to suggest you to create a expand table with detail days between that date range and link to original table.
Then you can use new table date column as source of slicer to filter original table.
Reference link:
Spread revenue across period based on start and end date, slice and dase this using different dates
For create relationship between two tables which not has unique records, I'd like to suggest you create a unique id table as bridge table to link two tables.
Regards,
Xiaoxin Sheng
Thanks for the response Xiaoxin. This feature was pretty low on my priority list. I can always create a relationship to another date table so the slider controls the histogram.
My other issues are more pertinent and are unanswered. I was hoping for some direction and best practices from the community. See "Suspected Problems" 1-3.
My most pressing issue is how to get histogram to show FTE's over time in the histogram without expanding every date between which causes total number of rows/records to grow exponentially (42 rows turns into 16k rows). My real data will have potentially hundreds or a few thousand rows (all positions showing single start and finish date).
Hi Anonymous,
>>My most pressing issue is how to get histogram to show FTE's over time in the histogram without expanding every date between which causes total number of rows/records to grow exponentially (42 rows turns into 16k rows).
Yes, it will cause the performance issue when you use this method.(good part: can direct use filter/slicer to interaction with detail date in date range; bad part: will increase records amount case the performance issue)
If that is a case, you need some additional operations to indirect operate records.(create unrelated calendar table as source of slicer, write measure to compare selected value and current row contents to return flag, use flag measure as visual level filter to filter unmatched records)
Notice: due to above operation are between unrelated tables, so you need to enable flag measure as visual level filter on all of visuals which you need to filter unmatched records)
Regards,
Xiaoxin Sheng