Forum Discussion

Lucasvd's avatar
Lucasvd
Frequent Visitor
4 years ago

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

 

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())

 

 

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. 

Need Help

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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 )))
    • Lucasvd's avatar
      Lucasvd
      Frequent Visitor
      4 years ago

      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
    )