Filtering slicer based on values in a related table

[Sachintha]

I have a dataset of employee work hours logged. The employees will log their work hours each day - key point being that not every employee will log hours for every day. These employees are shift workers, hence they may only work on certain days. The dataset looks like below. PayPeriod is payroll related, and is important because in my visual I want to show data only for the days for which PayPeriod is Current.

Date,Employee,Hours,PayPeriod
2024-11-01,Chelsea,9,Previous
2024-11-01,Jane,8,Previous
2024-11-01,Mike,8,Previous
2024-11-02,Chelsea,4,Previous
2024-11-02,Jane,5,Previous
2024-11-03,Mike,6,Current
2024-11-04,Chelsea,5,Current
2024-11-04,Jane,2,Current
2024-11-04,Mike,8,Current
2024-11-05,Chelsea,8,Current
2024-11-05,Jane,8,Current
2024-11-05,Mike,8,Current
2024-11-06,Chelsea,9,Current
2024-11-06,Jane,9,Current
2024-11-06,Mike,9,Current
2024-11-07,Chelsea,3,Current
2024-11-07,Jane,6,Current
2024-11-08,Mike,6,Current
2024-11-09,Chelsea,7,Current
2024-11-09,Jane,5,Current
2024-11-09,Mike,6,Current
2024-11-10,Jane,2,Current
2024-11-10,Mike,7,Current
2024-11-11,Chelsea,8,Current
2024-11-12,Chelsea,6,Current
2024-11-12,Jane,8,Current
2024-11-12,Mike,8,Current
2024-11-13,Chelsea,12,Current
2024-11-13,Jane,7,Current
2024-11-13,Mike,6,Current
2024-11-14,Chelsea,7,Current
2024-11-14,Jane,6,Current
2024-11-14,Mike,3,Current

My task is to show data in a Matrix for only the Current PayPeriod. Additionally, the Matrix must highlight any days where an employee has not entered any work hours. Based on the dataset above, my result should look like this:

Additionally, I need to give two slicers to the users: Work Week and Employee. The idea is to let the users filter by WW and Employee, and quickly identify the days for which they haven't done any work hour entries. An example of a filtered visual would be this:

To achieve this, I imported the data set and added a WW column using the WEEKNUM() DAX function. Then I created a DateTable, which ranges from 2024/1/1 to 2024/12/31 (In my actual report, there are other tables, so the DateTable range will inevitably be larger than the date range of work hours table, which is a crucial point).

WW Column in WorkHours table:

WW = "WW-" & WEEKNUM([Date])

DateTable:

DateTable = 
    ADDCOLUMNS(
        CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31)),
        "WW", "WW-" & FORMAT(WEEKNUM([Date]), "00")
    )

I linked the two tables using the Date columns.

Now, to achieve the above visual, first I added a 'Filters on this page' filter for PayPeriod = Current.

Next, I populated the Matrixs as follows. Basically, Rows and Values are from the WorkHours table while Columns are from the DateTable.  All 3 of Employee, WW, and Date values are also set to 'Show items with no data' as well.

Then I added two slicers for WW and Employee, and the WW slicer gets data from the DateTable, not the WorkHours table. Finally, I added conditional formatting to the Matrix to highlight blank values:

This works almost fine.

The issue is, because the WW slicer is using the DateTable WW, it shows all 53 work weeks for the year instead of the desirable two work weeks where PayPeriod = Current. In other words, I'd like the WW slicers to only present the two WWs for which the PayPeriod is current in WorkHours table - in this case WW45 and W46.

How can I achieve this?

EDIT
Sample PBIX file:

https://drive.google.com/file/d/1EfzR6KB29psjusEYG2LlovV2IZPXKuxi/view?usp=sharing

[Sachintha · Accepted solution]

Unfortunately none of the replies seem to address the core issue, so I dived into this a bit and came up with a solution.

1. Create a separate WorkWeek table in PowerQuery.
   I created a separate table called WWs by referncing the WorkHours table, added the WW column, and removed duplicates based on WW. Notice the Table.Buffer() encompassing the Table.Sort(), which is necessary when removing duplicates and wanting to adhere to the sort order. Finally, I filtered to keep only the WWs where PayPeriod = Current.

let
    Source = WorkHours,
    #"Added Custom" = Table.AddColumn(Source, "WW", each "WW-" & Text.From(Date.WeekOfYear([Date]))),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Custom",{{"Date", Order.Descending}})),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"WW"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates",{"WW", "PayPeriod"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([PayPeriod] = "Current"))
in
    #"Filtered Rows"​

The resulting table looks like this:

I did NOT set a relationship between this and the other tables.
Also, note that I no longer need the WW column in the WorkHours table.

• Add a CurrentWWs column to the DateTable
  I created the DateTable same as before, and linked with the WorkHours table by Date.

DateTable = 
    ADDCOLUMNS(
        CALENDAR(DATE(2024, 1, 1), DATE(2024, 11, 30)),
        "WW", "WW-" & FORMAT(WEEKNUM([Date]), "00")
    )​

Then I added a CurrentWWs column by LOOKUPVALUE() on WWs table.

CurrentWWs = LOOKUPVALUE(WWs[WW], WWs[WW], DateTable[WW])​

The resulting DateTable looks like below. Essentially, the CurrentWWs column has the WW if it falls within a WW where the PayPeriod is Current in the WorkHours table. Otherwise, empty.

• Add 'Filters on this page'
  Add a 'Filters on this page' using the CurrentWWs column, set it to display the CurrentWWs that are not blank.
  
• Set the Matrix and Slicers as below
  WW Slicer using WW column in DateTable.
  

  Matrix as below. Note the WW and Date is from the DateTable.

  This will yield the expected result, while the WW filter only showing the WWs for which PayPeriod = Current.
  

[Tutu_in_YYC]

If this is what you want:

You just need to uncheck this:

[Sachintha]

As mentioned in my original post, I can get this part to work.

My question is how do I get the WW dropdown to show only the WWs for which entries are marked as PayPeriod = Current. In other words, based on data, only the WWs 45 and 46 are 'current' WWs. As such, I want the dropdown to show only those two WWs as options that the user can select.

[Ashish_Mathur]

Hi,

PBI file attached.  Please apply the conditional formatting again

[Sachintha]

@Ashish_Mathur this is not doing what I need.

1. I want to filter by WW and Employee, and when I do so, it should show each day of the week even if they have not entered a value for that day. But it should NOT show any days that's outside the PayPeriod = Current window.

2. I need the WW slicer to also show only the WW's that are in the PayPeriod = Current window.

[Ashish_Mathur]

Hi,

Drag this measure

H = COALESCE(SUM(WorkHours[Hours]),0)

Hope this helps.

[Sachintha]

@Ashish_Mathur 

This still does not address the main issue. As mentioned in my original post, in my own PIBX file I can already get the matrix to display dates without any data entered and have background formatted. This part works fine.

My question is how do I get the WW dropdown to show only the WWs for which entries are marked as PayPeriod = Current. In other words, based on data, only the WWs 45 and 46 are 'current' WWs. As such, I want the dropdown to show only those two WWs as options that the user can select.

[SachinNandanwar]

Can you share the PBIX file with the sample data ?

[Sachintha]

@SachinNandanwar here you go.

https://drive.google.com/file/d/1EfzR6KB29psjusEYG2LlovV2IZPXKuxi/view?usp=sharing