topic Re: Pulling data between two tables. in DAX Commands and Tips

Pulling data between two tables.

Nick221_ — Thu, 16 Mar 2023 14:41:45 GMT

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])))

Re: Pulling data between two tables.

johnt75 — Thu, 16 Mar 2023 15:19:19 GMT

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.

Re: Pulling data between two tables.

Nick221_ — Thu, 16 Mar 2023 15:21:42 GMT

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

Re: Pulling data between two tables.

Nick221_ — Thu, 16 Mar 2023 15:33:17 GMT

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.

Re: Pulling data between two tables.

johnt75 — Thu, 16 Mar 2023 15:39:03 GMT

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 )

Re: Pulling data between two tables.

Nick221_ — Thu, 16 Mar 2023 15:40:17 GMT

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