TransferDate =
VAR DeptMatch = --If the transaction date column is not blank, this counts the number of matches it has between departments
CALCULATE(
COUNTROWS(FILTER('ICS_PhoneExt_8x8', 'ICS_PhoneExt_8x8'[8E_TransDate] <> BLANK())),
'ICS_PhoneExt_8x8'[8E_Dept] = 'Call Detail Record'[Departments]
) > 0
VAR ExtMatch = --If the transfer date column is not blank, this comparecounts the number of matches of 8x8 extension with the current extension being used in 8x8
CALCULATE(
COUNTROWS(FILTER('ICS_PhoneExt_8x8', 'ICS_PhoneExt_8x8'[8E_TransDate] <> BLANK())),
'ICS_PhoneExt_8x8'[8E_Ext] = VALUES('Call Detail Record'[AA_Destination])
) > 0
RETURN --if true it uses original data from the Call Detail Record, however if false it uses the ICS PhoneExt_8x8 table data.
IF(
DeptMatch,
'Call Detail Record'[Departments],
IF(
ExtMatch,
'Call Detail Record'[AA_Destination],
IFERROR(
VALUES('ICS_PhoneExt_8x8'[8E_Dept]),
VALUES('ICS_PhoneExt_8x8'[8E_Ext])
)
)
)
This code returns the error that the expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression. I tried to comment my code so you could understand my intention as well.
