Forum Discussion
Advocate IIFILTER only positive values
Suppose the lowest level of your hierarchy in your visual is Table1[Item]. Then you need to iterate over each of those items.
Availability ( hours + ) = SUMX ( VALUES ( Table1[Item] ), VAR __available = [Target Capacity] - [Planned Hours ( Total )] RETURN IF ( __available > 0, __available ) )You can use other columns from related tables. See the documentation.
It might work better to start with Skills and use Staff[Name] though.
Availability = VAR Summary = SUMMARIZE ( Skills, Staff[Name], Skills[Skill], Skills[Experience level], "@Hours", [Target Capacity] - [Planned Hours ( Total )] ) RETURN SUMX ( FILTER ( Summary, [@Hours] > 0 ), [@Hours] )
Super UserHow about this?
Availability =
VAR Summary =
SUMMARIZE (
Staff,
Staff[Name],
Skills_DB[Skill],
Skills_DB[Experience level],
"@Hours", [Target Capacity] - [Planned Hours ( Total )]
)
RETURN
SUMX ( FILTER ( Summary, [@Hours] > 0 ), [@Hours] )
Advocate IIUnfortunately, no...
- AlexisOlson
I can't easily debug name errors without access to the file. I can't even tell if your table is Skills_DB or Skills since you have screenshots with both variations.
- Delphia
Sorry, it's the same table, I just renamed it. If I understand correctly the error, the problem is that in SUMMARIZE function in your formula the table that is used is Staff (the first parameter), but then you use columns from another table.
- AlexisOlson
You can use other columns from related tables. See the documentation.
It might work better to start with Skills and use Staff[Name] though.
Availability = VAR Summary = SUMMARIZE ( Skills, Staff[Name], Skills[Skill], Skills[Experience level], "@Hours", [Target Capacity] - [Planned Hours ( Total )] ) RETURN SUMX ( FILTER ( Summary, [@Hours] > 0 ), [@Hours] )