Mulitply columns from 2 fact tables (not directly related, but through 2 dimension tables)
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] ) ) ) )
