Forum Discussion
Pulling data between two tables.
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.
Below formula will not work because column "Completion time is Date/time formatted.
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 )
5 Replies
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.
When I remove the "Not Submitted" text, I am still receiving the same error.
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 )
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.I actually removed the If() part of the formula and they seemed to work.
