Forum Discussion
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 ...
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
)
Sorry, can't see why that's not working