Different results when using the Direct Query vs. Import options

[jhenscheid]

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.

 Any assistance would be appreciated.

[Eric_Zhang]

---

@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?

[jhenscheid]

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