Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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');
Solved! Go to 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
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.
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
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
User | Count |
---|---|
83 | |
77 | |
70 | |
69 | |
54 |
User | Count |
---|---|
106 | |
96 | |
90 | |
79 | |
68 |