Filtering by Week Number over Multiple Week Number Columns

[DaPiranha]

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 list.

The list has 8 columns for clocking in and 8 for clocking out of a job and the job can be clocked into over multiple weeks. I've added columns for the week number for each clock but is there a way to filter by week number across all 8 columns?

At the moment when I filter by week 9 in Clock 1 it filters out ID 27 because it didn't start in week 9

Is there anyway to filter using all of the 8 clocks? So I could filter by Week 9 and it would bring up ID 15 -1, ID 16 - 1,2,3 and ID 27 - 5,6,7,8

Thank you for your help

[GeorgeBuster]

Hi Dapiraha,

At first sight, I'd use a parameter table.

In the table visualization, you'll need to use a measure related to this table. This way, you can put in the slicer the attribute of the parameter table and filter all the columns.

I have a question for you, How or where do you get the time data of the Sharepoint use? I have a project where this kind of data would be very useful. Thank you.

Best,

Jorge Bustillo.

[DaPiranha]

Hi Jorge,

Thanks for the repsonse, as a newbie, i'm not 100% what you mean by parameter table or how to connect them? I've created a separate table with the week numbers in it but I can't link it across 8 columns

I actually use a PowerApps form to have inputs for Clock In 1, Clock Out 1 for the 8 columns which feed into sharepoint which then feeds into Power BI. I then have created columns for the time difference and then one which converts this into an hours and minutes column

[GeorgeBuster]

Hi Dapiranha,

Please, share a screenshot of the data (headers and few rows) and I'll try to help you with the measure.

Thank you very much, I'll need to look into PowerApps deeper.

Best,

Jorge Bustillo.

[DaPiranha]

Ok so here are the columns for clock ins and clock outs

Then they get converted to minutes in the individual Clock columns, which get summed in Clock total, then converted to hours and minutes in Clock Total Hours & Minutes. The Week Number Clock 2 etc are taken from the clock in columns because the clock in and out can only ever be the same day but the job itself can be actioned serveral times per day or over several days over several weeks

Thanks again for your help

[GeorgeBuster]

Hi Dapiranha,

Here you have a simple solution. This can be developed in the way your visualization needs.

Firstly you need a Parameter Table and your data table.

These tables haven't got a relationship in the model. However, they are linked by the measures. In this example, there are many measures as clock columns. In a better situation (different data), it would be necessary only one measure and she would be split by the attribute.

Well, there are two nested IFs and the hasonevalue to test if there is only one parameter selected. Put these measures in a matrix visulization and there it is.

Hope this could help you,
Jorge Bustillo

[DaPiranha]

Hi Jorge,

Thanks for coming back to me

The C1 Measure you've given I can't replicate becuase the Week Number Clock 1 etc in my datatable are columns not measures so it won't let me include them in the measure you've given

David

[GeorgeBuster]

Hi Dapiranha,

I know they are columns, in my example too.

Where I write DataTable you must use the name of your table and my columns Clock1, Clock2... are your Week Number Clock 2, Week Number Clock 3...

If you can achieve it, please, show me a screenshot of the error you have when author the measure.

Best,

Jorge Bustillo.

[DaPiranha]

Here's what I have:

I'm adding the measure to the Central Maintenance Database

[GeorgeBuster]

Please, try using "," instead of ";".

[DaPiranha]

Hi Jorge,

Thanks for that, the i've been able to add the measure as you suggested and have added the matrix visualisation. Im still having issues with the filter though.

This is it without the filter

I've added the new measures in and the old columns are there for reference. The "Week" dropdown filter at the top is linked to the parameter that I set up

When I then filter that by Week 9 which should bring up ID, 15, 16 & 27 I get this instead

So it's cutting out ID 27

[GeorgeBuster]

I see, show me a screenshot with the week 10 and other with the 11 selected to try to find a pattern.

Also, clean the aggregation function (Sum of) in the column of the Clock 1.

[DaPiranha]

Here you go:

It seems to be filtering by Clock 1 or C1 Measure still

The Sum of is happening to all of the clock columns but only changes the name of clock 1 for some reason

It's also adding the time from all the clocks too. In the picture from before which shows the errors in the measures to the filtered by week 9 the total time for ID 16 doesn't change whereas clock 10 should have dropped out of it and not be included

[GeorgeBuster]

Ok, in the slicer Week, what field are you setting? You must use the field of the parameter table, this field hasn't a relationship with your Week Number Clock columns and shouldn't affect them.

[DaPiranha]

The slicer "Week" is taken from Parameter Name in my Week Number Parameter that I set up

The parameter columns are here:

And the Clock Measures with them factored in in case i've put the wrong things in are here:

[GeorgeBuster]

Well, the parameter table is isolated in the model?

Try changing in the measures Blank() by "Nothing".

In the parameter table if the columns are the very same you don't need both, just one to use it in the measure and slicer.

[DaPiranha]

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]

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

[DaPiranha]

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

[GeorgeBuster]

Hi Dapiranha,

Do you have access to the Power BI Query Editor? There you can transform your data.

[DaPiranha]

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