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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Lucasvd
Frequent Visitor

Counting duplicate in a matrix

Hi everyone,

I am having some trouble with my project planning.

 

My project data set looks like this:

 

ID - ID - project name -Employee - Function - status- days - start date - end date

Schermafbeelding 2022-04-26 163925.png

 

With the following measure I create a Matrix visual of the staff members, and the date table and when they are scheduled.

 

 

Visual Project planning = 
VAR start_date =
CALCULATE(
           MIN('Input - planning'[Start date])
           , ALL('Date table')
         )
VAR end_date =
CALCULATE(
           MAX('Input - Schedule'[End date])
           , ALL('Date table')
          )

VAR _period =
              MIN('Date table'[Date]) >= start_date
              && MAX('Date table'[Date]) <= end_date
RETURN
IF(_period,1,BLANK())

 

Schermafbeelding 2022-04-26 163450.png

 

But I would like to know if a staff member is scheduled twice. Any tips? Cause it seems like i cant use count, cause its not a table. 

3 REPLIES 3
johnt75
Super User
Super User

You could create the below as a measure and put it on a table visual with employees

Employee double booked =
var currentEmployee = SELECTEDVALUE('Project'[Employee])
var empTable = GENERATE(
SELECTCOLUMNS( FILTER( 'Project', 'Project'[Employee] = currentEmployee),
"@start date", 'Project'[Start date], "@end date", 'Project'[end date]),
DATESBETWEEN( 'Date'[Date], [@start date], [@end date])
)
var summaryTable = ADDCOLUMNS( SUMMARIZE( empTable, [Date]), "@num rows", CALCULATE(COUNTROWS(empTable)) )
return NOT( ISEMPTY( FILTER(summaryTable, [@num rows] > 1 )))

Hi @johnt75 ,

Thanks for the answer but that would only result in a 'true' value on the start date of every project. 

 

 

Colum values Personeels planning = 
VAR currentEmployee =
    SELECTEDVALUE ( 'Input - personeel'[Naam] )
VAR empTable =
    GENERATE (
        SELECTCOLUMNS (
            FILTER ( 'Input - planning', 'Input - planning'[Medewerker] = currentEmployee ),
            "@start date", 'Input - planning'[Startdatum],
            "@end date", 'Input - planning'[Einddatum]
        ),
        DATESBETWEEN ( 'Date table'[Date], [@start date], [@end date] )
    )
VAR summaryTable =
    ADDCOLUMNS (
        SUMMARIZE ( empTable, [Date] ),
        "@num rows", CALCULATE ( COUNTROWS ( empTable ) )
    )
VAR start_datum =
    CALCULATE ( MIN ( 'Input - planning'[Startdatum] ), ALL ( 'Date table' ) )
VAR eind_datum =
    CALCULATE ( MAX ( 'Input - planning'[Einddatum] ), ALL ( 'Date table' ) )
VAR _periode =
    MIN ( 'Date table'[Date] ) >= start_datum
        && MAX ( 'Date table'[Date] ) <= eind_datum
RETURN
    //IF(_periode,1,BLANK()) 
    IF (
        ( ISEMPTY ( FILTER ( summaryTable, [@num rows] > 1 ) ) ),
        ( IF ( _periode, 1, BLANK () ) ),
        2
    )

 

 

Schermafbeelding 2022-04-28 092340.png

Sorry, can't see why that's not working

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors