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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Nick221_
Frequent Visitor

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.

 

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

View solution in original post

5 REPLIES 5
Nick221_
Frequent Visitor

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

 

Nick221_
Frequent Visitor

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
Super User
Super User

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
    )

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.