Best way to write a measure summing an amount from a parent table filtering on column in child?

[jhills]

What is the best way to write a measure to calculate sum of amount from Opportunity where the current user is either the owner of the Opportunity(Parent) or on the Opportunity Team(child)?

Here are the two tables and the result the measure should return.

Opportunity:

Opportunity ID	Amount	Opportunity Owner Email
1	100	Alice@company.com
2	200	Bob@company.com
3	300	Alice@company.com

Opportunity Team:

Opportunity Team ID	Opportunity ID	Team Member Email
1000	1	Alice@company.com
1001	1	Sam@company.com
1003	1	Jane@company.com
1004	2	Sam@company.com
1005	2	Jane@company.com
1006	3	Sam@company.com

Results

Alice	400
Bob	200
Jane	300
Sam	600

It is a one to many relationship between parent and child tables based on Opportunity ID.

Here is the Dax calculation I currently have that is working. I am using direct query and trying to improve performance. Is there a better way to calculate sum of amount when the current user is either the opportunity owner or on the opportunity team?

var CurrentUser = USERPRINCIPALNAME()

return

CALCULATE(SUM(' Opportunity '[Amount]),

    FILTER(

            ' Opportunity ',

            ' Opportunity '[Opportunity Owner Email] = CurrentUser ||

            CALCULATE(COUNTROWS(

                                FILTER(

                                            ' Opportunity Team ',

                                            ' Opportunity Team '[Team Member Email] = CurrentUser

                               )

           )) > 0

   )

)

[v-lid-msft]

Hi @jhills ,

Your measures seem pretty suitable In your scenario, We make some tiny change in it as following:

Measure 2 =
VAR CurrentUser =
    USERPRINCIPALNAME ()
RETURN
    CALCULATE (
        SUM ( 'Opportunity'[Amount] ),
        FILTER(
            'Opportunity',
            'Opportunity'[Opportunity Owner Email] = CurrentUser
                || CurrentUser in CALCULATETABLE(DISTINCT('Opportunity Team'[Team Member Email]))
        )      
)

You can compare the measure with previous one and find which one is faster in your scenario.

Best regards,

[jhills]

Thanks @v-lid-msft I will give it a try.