Help with nested self join to get the max of a field based on keys being passed from parent

[crl]

Hi

i am brand new to power BI.  i have this oracle SQL query that returns the data needed. now want to do this as a report in BI.  got so far as importing the tables and basic filtering. but not sure how to get the child select to return only one row [distinct] based on the nested select.  The last AND field=select max...  Any help is greatly appreciated.

SELECT DISTINCT
AL1.SPRIDEN_PIDM,
AL1.SPRIDEN_FIRST_NAME,
AL1.SPRIDEN_LAST_NAME,
AL2.SGBSTDN_STST_CODE,
AL2.SGBSTDN_TERM_CODE_EFF,
AL3.SFBETRM_TERM_CODE,
AL3.SFBETRM_RGRE_CODE,
AL4.GOREMAL_EMAIL_ADDRESS
FROM
SATURN.SPRIDEN AL1,
SATURN.SGBSTDN AL2,
SATURN.SFBETRM AL3,
GENERAL.GOREMAL AL4
WHERE
(AL2.SGBSTDN_PIDM=AL1.SPRIDEN_PIDM
AND AL3.SFBETRM_PIDM=AL2.SGBSTDN_PIDM
AND AL4.GOREMAL_PIDM=AL1.SPRIDEN_PIDM)
AND (AL1.SPRIDEN_CHANGE_IND IS NULL
AND AL2.SGBSTDN_STST_CODE='AS'
AND AL2.SGBSTDN_LEVL_CODE IN ('GN', 'GR', 'PC', 'UG', 'UN')
AND AL2.SGBSTDN_COLL_CODE_1 IN ('00', 'AS', 'BS', 'CI', 'ED', 'GR', 'HH', 'PS')
AND AL3.SFBETRM_TERM_CODE='202010'
AND AL4.GOREMAL_EMAL_CODE='CP'
AND AL4.GOREMAL_STATUS_IND='A'
AND AL2.SGBSTDN_STYP_CODE IN ('0', 'C', 'N', 'R', 'T', 'V'))
AND AL2.SGBSTDN_TERM_CODE_EFF =
(select max(x.Sgbstdn_Term_Code_Eff)
from sgbstdn x
where x.sgbstdn_term_code_eff <='202010');

[v-frfei-msft]

Hi @crl ，

KEEPFILTERS should help in your scenario. If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[crl]

Hi Frank

i dont know how to use keepf ilters.  as i mentioned i am just starting with PBI.

so i have a few tables the sql query returns a row for each id, with a max term

for example the result would be this:

id             name      termcode

123       jane          201830

456       bob           201810

the merge query has this

id        name      termcode

123   jane         201740

123  jane          201830

456  bob          201830

so i want to filter this so that only the two bottom rows are shown on the report.  onlty one for jane as the max termcode is 201830.  does that make sense?  there is no calculation going on here.  just  picking the max termcode from the merged data rows.

thank you

[crl · Accepted solution]

Hi

i found a way to do it.  another thread mentioned below had the same problem, offered a solution that i tried that worked perfectly.

thank you

CRL

https://community.powerbi.com/t5/Desktop/Remove-duplicate-rows-based-on-max-value-of-a-different-col...