Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Custom measure

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 ...
  • PaulDBrown's avatar
    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