Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
LearnToFly
Frequent Visitor

Not sure how to rewrite...

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.
 
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Replace 

'ICS_PhoneExt_8x8'[8E_Ext] = VALUES('Call Detail Record'[AA_Destination])

with

'ICS_PhoneExt_8x8'[8E_Ext] IN VALUES('Call Detail Record'[AA_Destination])

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

Replace 

'ICS_PhoneExt_8x8'[8E_Ext] = VALUES('Call Detail Record'[AA_Destination])

with

'ICS_PhoneExt_8x8'[8E_Ext] IN VALUES('Call Detail Record'[AA_Destination])

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.