Forum Discussion

DaPiranha's avatar
DaPiranha
Helper I
8 years ago

Filtering by Week Number over Multiple Week Number Columns

Hi there,   I'm relativley new to Power BI and i'm trying to create a report from a Sharepoint site list. The report is to form a worksheet showing the time spent working on jobs in the sharepoint ...
DaPiranha's avatar
DaPiranha
Helper I
8 years ago

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

 

GeorgeBuster's avatar
GeorgeBuster
Advocate III
8 years ago

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