Custom measure

[GregVia]

Hello All,

I have the 2 following table:

Project	Roles	FTE budget			Resource	Roles	Project	Hours
AAA	JC	0			111	JC	AAA	2
AAA	CO	5			222	CO	AAA	1
AAA	SC	5			333	JC	BBB	5
BBB	JC	4			444	SC	CCC	2
BBB	CO	0			555	CO	BBB	3
BBB	SC	3			666	PM	CCC	7
BBB	PM	5			777	CO	CCC	4
CCC	JC	3
CCC	CO	3
CCC	SC	2
CCC	PM	2

Relationship is:

Project to project (1:*)

I need a mesaure to calculate the red column:

Project	Roles	FTE_Budget	Hours
AAA	JC	0	2
AAA	CO	5	1
AAA	SC	5	0
BBB	JC	4	5
BBB	CO	0	3
BBB	SC	3	0
BBB	PM	5	0
CCC	JC	3	0
CCC	CO	3	4
CCC	SC	2	2
CCC	PM	2	7

Could you please help me with this measure?

Many thanks

[PaulDBrown · Accepted solution]

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