Forum Discussion
Filtering by Week Number over Multiple Week Number Columns
Hi Jorge,
My apologies, last night I was tinkering to see what I can find and I had forgotten that I'd left a relationship active. This once deleted has made the Week dropdown work as you have shown.
This now only shows the week 9s or week 10s
This doesn't filter out visually to adjust the other columns. To show you what I mean, with 9 filtered on Week it doesn't adjust the Total Time card total and still shows the ID lines which aren't valid:
When I filter by Week Number Clock 1, it removes the excess and adjusts the Total Time
The final aim for the report is that it acts as a time sheet so I could select week 9 and show what jobs I worked on that week and the time spent working on that week.
Thank you again for your patience and help
Hi Dapiranha,
I've tried a workaround for you, but if you want to do more calculations I think you should get better your data by normalizing them. In my opinion to achieve all you want the best way to do it is creating a fact table instead of your denormalized table.
Here you have aprox the columns you'll need:
FactTable_pk - Id - Location - Week - ClockNumber - ClockIn - ClockOut - Duration
Hi Dapiranha,
Do you have access to the Power BI Query Editor? There you can transform your data.
Hi Jorge,
I see what you mean and that would work well I suspect. Sadly it's well beyond me at this stage to do the calculations, transforming etc to produce such a table and allow it to be refreshed when I update the sharepoint list.
Thank you for your help though, its been much appreciated
Hi Jorge
I do indeed have access but being able to transform the data correctly and successfully once i'm in there is a whole other story
I've managed to have a work around but creating this table:
This allowed me to use this filter in each of the Sum Clock Times:
Which filters the total time card I have how I wanted but not the main display table on the reporting screen
My next step is to also get the times to filter by person and in an ideal world get a filtered table to allow all of the corresponding jobs to be shown as filtered by the week number and person assigned to
At the moment I have this monstrosity
The individual tables are for the corresponding week numbers but the main week number slicer that adjusts the time doesn't affect the tables. The person slicer affects all the tables but not the total time
I'm getting closer!
I've amended the initial Clock measures to have this additional detail following creation of a Person table:
This allows me to filter the total time card by person and week number. I just need a way to filter the main table somehow so it corresponds