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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aashton
Helper V
Helper V

If statement pulling from 2 tables

Hello,

I have an employees table with Date, ID, Name, Location, etc.  The primary key would be the date/ID combination, as all employees and their status gets loaded on the first of every month.  I then have another table of paychecks, joining to the employees table on ID.  This is a many to many.  The paychecks table also has the location they worked and the date.  I want to find an accurate location for each employee, so I would first take the location off he paycheck, and if the employee did not have a paycheck I would take the location off the main employee table for the max date from each table.  Basically, IF Paycheck.Location is NOT NULL, then Paycheck.Location, EmployeeTable.Location.  I've tried creating this column from both tables, and it's not letting me pick fields from the other table.  I don't know how to do this.

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

Hi @aashton 

According to your description, whether your connection mode is direct query mode, because the computed column in direct query mode can only refer to the upper and lower rows of the current table, and cannot be queried across tables.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#modeling-limitatio...

If you use Direct Query mode and want to use computed columns across tables, you can refer to the following three methods

1.To change the connection mode from Direct Query to Import mode, you can refer to the following link.

Solved: Re: How to change direct query to import query - Microsoft Fabric Community

2.Create a measure that is not affected and can be queried across tables.

3.Create a calculated table (such as creating two replicated tables for these two tables), you can add calculated columns in these two replicated tables.

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

1 REPLY 1
v-xinruzhu-msft
Community Support
Community Support

Hi @aashton 

According to your description, whether your connection mode is direct query mode, because the computed column in direct query mode can only refer to the upper and lower rows of the current table, and cannot be queried across tables.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#modeling-limitatio...

If you use Direct Query mode and want to use computed columns across tables, you can refer to the following three methods

1.To change the connection mode from Direct Query to Import mode, you can refer to the following link.

Solved: Re: How to change direct query to import query - Microsoft Fabric Community

2.Create a measure that is not affected and can be queried across tables.

3.Create a calculated table (such as creating two replicated tables for these two tables), you can add calculated columns in these two replicated tables.

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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