Show Inverse selection listing of Date and Branch slicer

[Beavertron]

I have a workforce report which shows a listing of carers who worked during a period and which days along with a total of hours worked. This is driven by a calendar table and branch listing table to which I have two slicers on the report to control with, so the user can select branch then dates (to and from slider) and it shows you who worked in this period. The calendar table has various fields which allow for different drill down types.

Calendar table example;

Column names; Date, DowID, Weekstart, WeekID, MonthYear, MonthID, Year, Day, DayType, Quarter

Example values; 01 July 2020, 3, 29/06/2020, 1, Jul-20, 1, 2020, Wednesday, WD, 3

Example values; 02 July 2020, 3, 29/06/2020, 1, Jul-20, 1, 2020, Thursday, WD, 3

Officename table example;

Column names; Number, DBName, Description, Ops Manager, Officename

Example values; 1, db_Glasgow, Glasgow, Scotland, Care Glasgow

Example values; 1, db_IOW, Isle of Wight, South, Care Isle of Wight

My WorkforceDetailed table looks like this (one line per carer worked date and a total of hours)

WorkforceDetailed table example;

Column names; Officename, Carercode, CarerName, Actuals, TotalBookings, Hours

Example values; Glasgow, 301540, Mrs Parter, 07/07/2020, 3, 5.5

Example values; Glasgow, 301540, Mrs Parter, 08/07/2020, 2, 3

Example values; Isle of Wight, 745688, Mrs Seymour, 07/07/2020, 1, 2

What works perfectly is I have table link of Officename to WorkforceDetailed on the Officename column and Date from the calendar table to Actuals in the WorkforceDetailed table which allows me to select a Officename and Date period and it shows me who worked, to which I can total hours and create charts from. I can also work out a total of carers who were available to work but didn't on the date period. This measure was created by a WorkforceSummary table which lists the carers start and finish contract dates. By Creating the following measure I am able to give a count of these and then deducting this figure from the total rows in the table,

Avail in period = VAR endOfPeriod = MAX ( 'CalendarTable'[Date] )
VAR startOfPeriod = MIN( 'CalendarTable'[Date] )

RETURN CALCULATE
(
DISTINCTCOUNT('WorkforceSummary'[CarerKey]), FILTER ( ALL('WorkforceSummary'),
OR ( ( 'WorkforceSummary'[DateStarted] < endOfPeriod && 'WorkforceSummary'[LeaveDate] >= startOfPeriod), ( 'WorkforceSummary'[DateStarted] < endOfPeriod && 'WorkforceSummary'[LeaveDate] = BLANK())
)
))

I then created the following measure to show these results

Inactive in Period = [Avail in period] - ( DISTINCTCOUNT(WorkforceDetailed[Carercode]))

However I am trying to create a listing of the Carers that were inactive in the period, so those which have been filtered out by the Date slicer only. The Officename table join filter can still be applied as it would be ideal to see this by Office still. This would need to be in a table view listing Carercode, Forename and Surname. I can see that powerBI doesn't directly support this, but if there is a trick to this? I am a fairly new user to powerBI, so please provide steps for any additional version of a table to be created. It might not be possible for this to be done dynamically as well, so if the user changes the date sliders?

Thank you

[MFelix · Accepted solution]

Hi @Beavertron ,

Based on the information you have send where you have the following setup:

• Workforce Summary - All the users information
• Workforce Detailed - Information about when the workforce is working

Since you already are calculating the values of the workforce that is available, all of the users in the table WorforceSummary that are within the filter parameters, what you need to have is the ones that aren't working, so you need to filter out from the Avail in Period the ones not included in WorforceDetailed.

You can setup a measure similar to this one:

Inactive = 
VAR endOfPeriod =
    MAX ( 'CalendarTable'[Date] )
VAR startOfPeriod =
    MIN ( 'CalendarTable'[Date] )
VAR Work_Force =
    FILTER (
        SELECTCOLUMNS (
            FILTER (
                'WorkforceSummary',
                OR (
                     ( 'WorkforceSummary'[DateStarted] < endOfPeriod
                        && 'WorkforceSummary'[LeaveDate] >= startOfPeriod ),
                     (
                        'WorkforceSummary'[DateStarted] < endOfPeriod
                            && 'WorkforceSummary'[LeaveDate] = BLANK ()
                    )
                )
            ),
            "KEY", WorkforceSummary[Key]
        ),
        NOT ( [KEY] IN VALUES ( WorkforceDetailed[Key] ) )
    )
RETURN
    COUNTROWS ( Work_Force )

Has you can see I'm using the basis of your Activ measure and then making a new filter on top of it to take only the key values that are not in the workforcedetailed.

Now just use this measure on your visualizaiton and select all non blank values.

[Beavertron]

Thank you so much, incredible job and a perfect solution. I hope this helps other people as well.

[MFelix]

Hi @Beavertron ,

Since you want to present the values on a table visualization and based on the Avail in period you can try to do the following measure:

not available =
IF ( ISBLANK ( [Avail in period] ), 1 )

Now use this measure to filter out your table visualization.

[Beavertron]

Hi, thank you for the suggestion. Unfortunately that is just giving me a blank table. I am not sure if I am applying this correctly. So in the same table I have the measure for 'Avail in Period'. I have created the suggested measure above and applied this to the filters for this visual only and select values greater than 0. I hope you can advise, thank you

[MFelix]

Hi @Beavertron 

if your table is already being filtered by the Avail in period that has values and then you apply a filter that blanks all of the avail in period your records will be empty.

You need to apply only the second measure to your table visualization.

If this does not work can you share a sample file with a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Beavertron]

Thank you, I have private messaged a sample of the report and data contained, all anonymised and password protected in private messages. I appreciate your help. The Inactive tab is where I am trying to list carers that don't have any work days within the Date Slicer period.