Mulitply columns from 2 fact tables (not directly related, but through 2 dimension tables)

[Anonymous]

Dear PowerBi Community,

i am kinda frustrated here and hope someone can help me with dax. I have a data model that holds two fact tables fct_contract_employee and fct_time_tracking. The tables are related through the dim tables as you can see in the data model. I just cant figure out a dax formula to multiply those two.

This doesnt work because there is no direct relation between the two tables: 

Measure = SUMX(Contracts, Table1[Column1] * RELATED(Table2[Column2])) 

Filtering based on the dim columns doesnt work either: 

SUMX (FCT_TIME_TRACKING_CONTRACT_LEGACY, FCT_TIME_TRACKING_CONTRACT_LEGACY[TRACKED_WORKING_DAYS] * CALCULATE( FCT_CONTRACT_EMPLOYEE[DAILY_RATE], FILTER( FCT_CONTRACT_EMPLOYEE, FCT_CONTRACT_EMPLOYEE[EMPLOYEE_ID] = RELATED( FCT_TIME_TRACKING_CONTRACT_LEGACY[employee_id]) && FCT_CONTRACT_EMPLOYEE[contract_id] = RELATED( FCT_TIME_TRACKING_CONTRACT_LEGACY[contract_id] ) ) ) )

[johnt75 · Accepted solution]

You can use something like

Amount =
SUMX (
    Contract,
    VAR DailyRate =
        LOOKUPVALUE (
            Employee[Daily rate],
            Employee[ID], Contract[Employee ID],
            Employee[Contract ID], Contract[ID]
        )
    RETURN
        DailyRate * Contract[Hours worked]
)

[Anonymous]

Thanks alot John, this one worked for me: 

Measure =

SUMX (

    FCT_TIME_TRACKING_CONTRACT_LEGACY,

    VAR DailyRate =

        LOOKUPVALUE (

            FCT_CONTRACT_EMPLOYEE[DAILY_RATE],

            FCT_CONTRACT_EMPLOYEE[EMPLOYEE_ID], FCT_TIME_TRACKING_CONTRACT_LEGACY[EMPLOYEE_ID],

            FCT_CONTRACT_EMPLOYEE[CONTRACT_ID], FCT_TIME_TRACKING_CONTRACT_LEGACY[CONTRACT_ID]

        )

    RETURN

        DailyRate * FCT_TIME_TRACKING_CONTRACT_LEGACY[TRACKED_WORKING_DAYS]

)