Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
sarah2
Helper I
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  
DateNameSession IDVariable
2-JanAmy123a
2-JanAmy123b
2-JanBen124s
4-JanBen125a
4-JanAmy126s
4-JanBen120s
5-JanBen132s
6-JanAmy140a
9-JanAmy150a
9-JanAmy150s
1-FebJen164m
5-FebJen166a
5-FebAmy182v
2-MarMark202k
2-MarMark202b
2-MarMark309c

 

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
sarah2
Helper I
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

View solution in original post

3 REPLIES 3
sarah2
Helper I
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

v-weiyan1-msft
Community Support
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]

vweiyan1msft_0-1709881092963.png

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.

vweiyan1msft_1-1709881135497.png

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

vweiyan1msft_2-1709881156450.png

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

vweiyan1msft_3-1709881179325.png

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.

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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.