Filtering a DAX function based on a relationship

mmaleta85 — Tue, 02 May 2023 22:24:29 GMT

Suppose I have the following tables

Hosts:

Host Id	Host
1001	Abigail Morris
1002	Betty James
1003	Karla Carraso
1004	Lynn Williams

Players:

Player Id	Player
2001	Aaron Chambliss
2002	Michael Stewart
2003	Steve Jazz
2004	Wallace Stanley

Host-Player History:

Host Id	Player Id	Start Date	End Date
1001	2001	1/1/2023	12/31/2023
1001	2002	1/1/2023	4/30/2023
1002	2002	5/1/2023	12/31/2023
1002	2003	2/6/2023	6/14/2023
1003	2003	6/15/2023	12/31/2023
1003	2004	3/15/2023	8/12/2023
1004	2004	8/13/2023	12/31/2023

Player Activity:

Player Id	Date	Revenue
2001	5/6/2023	$1000
2002	3/15/2023	$300
2002	7/20/2023	$1500
2003	4/11/2023	$2000
2003	8/9/2023	$5000
2004	6/20/2023	$7500
2004	11/29/2023	$350

I have relationships between the Hosts, Players, and Host-Player History tables based on their respective Ids. I also have a relationship between Host-Player History and Player Activity based on Player Ids. I want to use these relationships to determine how much revenue each host generated while players were hosted to them, ie, when the date played is between the begin and end date for each host-player row, so that I can generate this type of report:

Host	Total Revenue
Abigail Morris	$1300
Betty James	$3500
Karla Carrasco	$12500
Lynn Williams	$350

What type of calculations and filters would I need to accomplish this?

topic Filtering a DAX function based on a relationship in DAX Commands and Tips

Filtering a DAX function based on a relationship