cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jhenscheid
Frequent Visitor

Different results when using the Direct Query vs. Import options

I am relatively new to Power BI.  I am not a programmer, but in working with one of my co-workers, he wrote a sql for me to bring back test results to display a trip (order) from our system.  I pasted the sql using the Direct Query mode option and I get the desired results.  However, when I try to bring back similar results using the Import mode option and joining the tables, I do not get the same results.  My guess is that I am joining the tables incorrectly or that there are options in modeling the data that I am either doing incorrectly or not aware of.

 

The sql is;

with max_id as

(

select max(x.loadExtractRecords_pk) maxid

  from LoadExtractRecords x

 where x.LoadID = '55630017'

 ) select *

     from max_id

        join LoadStops ls on ls.LoadExtractRecords_FK = max_id.maxid

 

The results are;

 

maxidLoadStops_PKLoadExtractRecords_FKStopIDTypeType_idSequenceNumStartDateStartDate_ignoreTime

1818391

5652257

1818391

120192250

Pick

1000

0

5/19/2016 5:13:00 AM -05:00

FALSE

1818391

5652258

1818391

120192251

Drop

1001

1

5/19/2016 10:00:00 AM -05:00

FALSE

1818391

5652259

1818391

120192252

Drop

1001

2

5/19/2016 10:00:00 AM -05:00

FALSE

1818391

5652260

1818391

120192253

Drop

1001

3

5/19/2016 10:00:00 AM -05:00

FALSE

1818391

5652261

1818391

120192254

Drop

1001

4

5/19/2016 10:00:00 AM -05:00

FALSE

1818391

5652262

1818391

120192255

Drop

1001

5

5/19/2016 10:00:00 AM -05:00

FALSE

1818391

5652263

1818391

120192256

Drop

1001

6

5/19/2016 10:00:00 AM -05:00

FALSE

1818391

5652264

1818391

120192257

Drop

1001

7

5/19/2016 10:00:00 AM -05:00

FALSE

1818391

5652265

1818391

120192258

Drop

1001

8

5/19/2016 11:00:00 AM -05:00

FALSE

1818391

5652266

1818391

120192259

Drop

1001

9

5/19/2016 8:00:00 AM -05:00

FALSE

1818391

5652267

1818391

120192260

Drop

1001

10

5/19/2016 10:00:00 AM -05:00

FALSE

1818391

5652268

1818391

120192261

Drop

1001

11

5/19/2016 10:00:00 AM -05:00

FALSE

1818391

5652269

1818391

120192262

Drop

1001

12

5/20/2016 10:00:00 AM -05:00

FALSE

1818391

5652270

1818391

120192263

Drop

1001

13

5/20/2016 10:00:00 AM -05:00

FALSE

1818391

5652271

1818391

120192264

Drop

1001

14

5/20/2016 8:30:00 AM -05:00

FALSE

1818391

5652272

1818391

120192265

Drop

1001

15

5/20/2016 8:30:00 AM -05:00

FALSE

1818391

5652273

1818391

120192266

Drop

1001

16

5/20/2016 9:00:00 AM -05:00

FALSE

1818391

5652274

1818391

120192267

Drop

1001

17

5/20/2016 9:00:00 AM -05:00

FALSE

 

 

This is how I have the 2 tables connected.

 

 

image.png

 

 

 Any assistance would be appreciated.

 

2 REPLIES 2
Eric_Zhang
Microsoft
Microsoft


@jhenscheid wrote:

I am relatively new to Power BI.  I am not a programmer, but in working with one of my co-workers, he wrote a sql for me to bring back test results to display a trip (order) from our system.  I pasted the sql using the Direct Query mode option and I get the desired results.  However, when I try to bring back similar results using the Import mode option and joining the tables, I do not get the same results.  My guess is that I am joining the tables incorrectly or that there are options in modeling the data that I am either doing incorrectly or not aware of.

 


The relationship in the snapshot looks good. What did you do to get the same results? Create a calculated table or what? Is it possible for your to upload the importmode pbix file?

Just to be clear, when I used the sql as a direct query, I got the desired results but they were not the same as when I used the Import mode.  Unfortunately, I am not able to currently upload the import mode file.  If needed for a solution, I can try to do this.  I think the main difference or is in the sql I can bring back the max row on the table for the primary key.  How would I do that in Power BI?  Is that a group function or is there a way to set up a calculation to bring back the max row?

 

Thanks

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors