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 ...
Need Help
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
    )

     

     

    • johnt75's avatar
      johnt75
      Super User
      4 years ago

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