cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## List of staff with no data in a time period

Hello! I have a data set that contains names of staff members and dates. Ultimately I want to know which staff members I DON'T have data for in a certain month or time period (changed via date slicer).

-other notes about my table in case it impacts the solution: a staff member might have multiple rows for the same date because of different variables. All staff members are on the main data table at some point, but they might not be in every month. I also need to be able to use a slicer to change the date range I'm looking at. I've seen other people recommend a date table which I can make, but I'm not sure how to use it in this context.

Here's a sample of the Main Data Table:

 Main Data Table Date Name Session ID Variable 2-Jan Amy 123 a 2-Jan Amy 123 b 2-Jan Ben 124 s 4-Jan Ben 125 a 4-Jan Amy 126 s 4-Jan Ben 120 s 5-Jan Ben 132 s 6-Jan Amy 140 a 9-Jan Amy 150 a 9-Jan Amy 150 s 1-Feb Jen 164 m 5-Feb Jen 166 a 5-Feb Amy 182 v 2-Mar Mark 202 k 2-Mar Mark 202 b 2-Mar Mark 309 c

I also have a table with current staff listed (optional for the solution)

 Current Staff List Amy Ben Jen Mark

The end product I'm looking for is a table that lists which name is missing on the Main Data Table. Example: if I have a slicer set for January, I would like a table that lists: Jen, Mark. If I have the slicer set for February, I would like the table to list: Ben, Mark
My understanding is that a merged table would work once but I'm not sure how to get it to work more long-term without manually doing it for each month.
Thank you!!

1 ACCEPTED SOLUTION
Helper I

With inspiration from this post I solved this by making a calculated table of all of the names
TABLE= SUMMARIZE( 'Main Data Table', 'Main Data Table'[Name])

Then connected the relationship to the Main Data Table (one to many). On the report I then made a measure:
Measure= CALCULATE(DISTINCTCOUNT('Main Data Table'[Session ID])+0

With a table in the report I added the names from my new Table and this measure which resulted in what I wanted - a count of session IDs including showing zeros

3 REPLIES 3
Helper I

With inspiration from this post I solved this by making a calculated table of all of the names
TABLE= SUMMARIZE( 'Main Data Table', 'Main Data Table'[Name])

Then connected the relationship to the Main Data Table (one to many). On the report I then made a measure:
Measure= CALCULATE(DISTINCTCOUNT('Main Data Table'[Session ID])+0

With a table in the report I added the names from my new Table and this measure which resulted in what I wanted - a count of session IDs including showing zeros

Community Support

Hi @sarah2 ,

Based on the example and description you provided, please try the following steps:
1.You can create Calculated table.

``Table = DISTINCT('Main Data Table'[Date])``

Then try code as below to create Calculated column.

``Month = 'Table'[Date].[Month]``
``MonthNumber = 'Table'[Date].[MonthNo]``

2. Use the following code to create a measure.

``````Measure =
VAR SelectedMonth =
SELECTEDVALUE ( 'Table'[Month] )
VAR Count_ =
COUNTX (
FILTER ( 'Main Data Table', 'Main Data Table'[Date].[Month] = SelectedMonth ),
'Main Data Table'[Name]
)
RETURN
IF ( ISBLANK ( SelectedMonth ), 1, IF ( Count_ >= 1, 0, 1 ) )
``````

The fields in the table are as follows.

Select table visual, put the measure in the "Filters on this visual" section, and filter it by "Measure is 1".

When you select "January" in the slicer, Result is as below.

For further detail, please find attachment.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

Thank you so much for your reply! I played around with it and was unsuccessful, so if you have time to continue to troubleshoot I would really appreciate it.

While making the calculated column for "Month" I get an error that says "Column reference to 'Date' in table 'Table' cannot be used with a variation 'Month' because it does not have any." I then attempted to just use the Date column instead of month, but it did not work with the slicer.

Additionally, I would like to retain a continuous date slicer instead of just categorizing by month (this is not a deal breaker though). My main issue is that I have multiple years of data.

Thank you!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors