Get Text Value in a measure from multiple table. COALESCE Function? SQL CASE Statement.

[P_R]

Hi, I am unable to write DAX Function for the following scenario. I am looking for 2 measures in my SalesTeam mesaures table:
1. SalesTeamName = List of all the SalesTeamName from SalesTeamInMonth Table for Current Month (in my case the latest month) when just getting SalesTeamName measure but when displaying Accountid and Month from RevenueDetails Table it should return the corresponding SalesTeam from SalesTeamInMonth Table.
2. ActiveSalesTeam = I need to use COALESCE here in order to get the value of active sales team from different tables. ORDER to get the values as below:

1. SalesTeamName From ActiveTeamsByAccounts Table
2. ActiveSalesTeam From SalesTeamInMonth Table
3. description From SalesTeamByReseller Table
4. SalesTeamName From SalesTeamToday Table

   In SQL, I write the below query to get my desired Result.

   SELECT

   STIM.TeamName AS SalesTeamName

   ,COALESCE ( ATA.SalesTeamName

   , STIM.SalesTeamName

   , STBR.description

   ,STT.SalesTeamName

   ,'Other'
   ) AS ActiveSalesTeam

   FROM RevenueDetails RD
   LEFT JOIN SalesTeamToday STT on STT.SalesPersonID = RD.SalesPersonID
   LEFT JOIN SalesTeamByReseller STBR on STBR.name =  RD.name

   LEFT JOIN SalesTeamInMonth STIM on STIM.SalesPersonMonthKey = RD.SalesPersonMonthKey

   LEFT JOIN ActiveTeamByAccount ATA on ATA.AccountKey = RD.AccountKey

   So let me explain. I have a RevenueDetail Table which have AccountID and corresponding salespersonID attached to it. Goal here to identify the salesperson and its active/current sales Team of an account in a specific month. But since our logic currently is a little funky and so not always provide correct information (due to missing or incorrectly tagged data) regarding to the salesperson by just joining SalesTeamToday Table on SalespersonID . So we have multiple scenarios where we identify the salesperson on a particular account at a particular time (hence 3 more tables) So now One of our table ActiveSalesTeambyAccount identify which SalesPerson and SalesTeam is currently assigned to the the account. Second Table SalesTeamInMonth identifies the salesperson and its team in a given month. Third Table is Sales Team by Reseller where we identify the Salesperson and its team which joins to Revenue table on a name field which identifies the account owner saleperson rather than operational Salesperson. Fourth Table is SalesTeamToday which gives salesperson and its team as of today. So the logic we need to build is for a transaction row in RevenueDetails Table identify the SalesTeamName from the 1st table where we have the latest saalesperson and salesteam assigned to the account then if the account has no assignment of salesperson then we go to second table where we get salesperson and its team in the month of transaction. if that is not available (salespersonid is null or salespersonid in the month of transaction is missing a sales team assignment) then we go to third table where we try to get the team of the account owner (also a salesperson) and then if we do not have name available in transaction then we go to fourth table where we get the current salesteam of the salespersonID in RevenueDetails Table. and if all of this doesn't work then we Hardcode the TeamName to 'Other'.

   Sample Data and .PBIX File:

   https://drive.google.com/drive/folders/1WQo75i4VOxKciy4-PX2U1ax3fLPWFFEk?usp=sharing

   Now there maybe other ways to handle this and i am trying to either fix the model or get the DAX function to get this Scenario. I am struggling on the same for quite sometime now. Let me know if this is possible or there is a better way to do this? Any Help is appreciated. Thanks in advance. 

    

   Model