Forum Discussion
Custom measure
- 4 years ago
Here is one way.
1) Create dimension tables (Modeling -> New Table) for the fields Project and Roles using the follwoing pattern:
DProject = DISTINCT ( UNION ( VALUES ( 'FTE Budget Table'[Project] ), VALUES ( 'Resource Table'[Project] ) ) )2) Set up single direction 1:* relationships between these dimension tables and their corresponding fields in the fact Tables. The model looks like this:
3) Create the following measures to use in the visual:
Sum FTE Budget = SUM('FTE Budget Table'[FTE budget])Sum Project Hours = IF ( ISBLANK ( [Sum FTE Budget] ), BLANK (), SUM ( 'Resource Table'[Hours] ) + 0 )4) create the visual using the fields from the Dimension tables and the measures to get:
I've attached the sample PBIX file
Here is one way.
1) Create dimension tables (Modeling -> New Table) for the fields Project and Roles using the follwoing pattern:
DProject =
DISTINCT (
UNION (
VALUES ( 'FTE Budget Table'[Project] ),
VALUES ( 'Resource Table'[Project] )
)
)
2) Set up single direction 1:* relationships between these dimension tables and their corresponding fields in the fact Tables. The model looks like this:
3) Create the following measures to use in the visual:
Sum FTE Budget =
SUM('FTE Budget Table'[FTE budget])Sum Project Hours =
IF (
ISBLANK ( [Sum FTE Budget] ),
BLANK (),
SUM ( 'Resource Table'[Hours] ) + 0
)
4) create the visual using the fields from the Dimension tables and the measures to get:
I've attached the sample PBIX file