Pulling data between two tables.

[Nick221_]

Below is the formula I am using on multiple columns. It will pull data from one table to another by my two Concat search coulmns (Concat Search & Column). However, I wanted to use this same formula for another column. In this case, "Completion time". I am receiving the error "Expressions that yield variant data-type cannot be useed to define calculated columns". Is there a way to use this formula to pull a date instead of a whole number?

Below is the working formula. This formula will pull the "custom" column that just contains "Yes" or "No" responses.

Completed = if(CALCULATE(LASTNONBLANK('Affiliate Submission'[Custom],1),FILTER(ALL('Affiliate Submission'),'Affiliate List'[Column]='Affiliate Submission'[Concat Search]))=blank(),"Not Submitted",CALCULATE(LASTNONBLANK('Affiliate Submission'[Custom],1),FILTER(ALL('Affiliate Submission'),'Affiliate List'[Column]='Affiliate Submission'[Concat Search])))

Below formula will not work because column "Completion time is Date/time formatted.

Completion Time = if(CALCULATE(LASTNONBLANK('Affiliate Submission'[Completion time].[Date],1),FILTER(ALL('Affiliate Submission'),'Affiliate List'[Column]='Affiliate Submission'[Concat Search]))=blank(),"Not Submitted",CALCULATE(LASTNONBLANK('Affiliate Submission'[Completion time].[Date],1),FILTER(ALL('Affiliate Submission'),'Affiliate List'[Column]='Affiliate Submission'[Concat Search])))

[Nick221_]

I actually removed the If() part of the formula and they seemed to work.

[Nick221_]

The formula is as follows:

if(CALCULATE(LASTNONBLANK('Affiliate Submission'[Completion time].[Date],1),FILTER(ALL('Affiliate Submission'),'Affiliate List'[Column]='Affiliate Submission'[Concat Search]))=blank(),CALCULATE(LASTNONBLANK('Affiliate Submission'[Completion time].[Date],1),FILTER(ALL('Affiliate Submission'),'Affiliate List'[Column]='Affiliate Submission'[Concat Search])))

I am not receving any errors, however it is not pulling any data. Just blanks.

[johnt75]

The problem is the "Not submitted". That is text whereas the actual values are datetime. You just need to use the value of the calculation as is, and if it is blank then leave it as blank.

[Nick221_]

When I remove the "Not Submitted" text, I am still receiving the same error.

[johnt75 · Accepted solution]

If the column is being added to the Affiliate List table you can try

Completion Time =
VAR CurrentColumn = 'Affiliate List'[Column]
RETURN
    CALCULATE (
        MAX ( 'Affiliate Submission'[Completion time].[Date] ),
        REMOVEFILTERS ( 'Affiliate Submission' ),
        'Affiliate Submission'[Concat Search] = CurrentColumn
    )