Forum Discussion
Kish1999
Helper II
Helper IIGet Hours for each project
Hello All, I have a scenario where I need to get the hours for each of the Projects. The project details are present in Projects table The hours are present in hours table and these are present for...
Instead of the relationship, you could create the Project Hours measure like
Project Hours = VAR ProjectKeys = CALCULATETABLE ( VALUES ( 'Project Key'[Key] ), TREATAS ( VALUES ( Project[Project Name] ), 'Project Keys'[Project Name] ) ) RETURN CALCULATE ( SUM ( Hours[Hours] ), TREATAS ( ProjectKeys, Hours[Key] ) )
johnt75
Super User
Super UserYou could create a bridge table like
Project Key = SELECTCOLUMNS(
GENERATE(
Project,
var clink = Project[Clink]
var currentName = Project[Name]
var release = Project[Release]
return UNION(
CALCULATETABLE( SELECTCOLUMNS( Issue, Issue[Key]), Issue[Clink] = clink && not(ISBLANK(Issue[Clink]))) ,
CALCULATETABLE( SELECTCOLUMNS( 'FV', 'FV'[Key]), 'FV'[Release] = release && NOT( ISBLANK( 'FV'[Release])))
)
),
"Name", Project[Name],
"Key", [Key]
)then link that to your Project table and create a measure like
Project Hours = CALCULATE( SUM(Hours[Hours]), TREATAS( VALUES('Project Key'[Key]), Hours[Key] ) )
Kish1999Helper II
Helper IIThank You johnt75 for the solution.This was very helpful. One thing i saw later was that in the Projects table, the release column is not necessarily blank wherever Clink is present. In some cases both release and Clink is present. In this case i have to pick up the keys from Clink.
- johnt75
You could add a further condition, like
CALCULATETABLE( SELECTCOLUMNS( 'FV', 'FV'[Key]), 'FV'[Release] = release && NOT( ISBLANK( 'FV'[Release])) && NOT( ISBLANK(clink)))