Create a Measure based on logic of SQL Query

[Anonymous]

Hi,

I have a SQL Query as below :

select s.TERM_START_DATE
, s.TERM_END_DATE
, S.MONTH_END_DATE
, S.COLLEGE_CODE
, s.LEVEL_CODE
, Round (SUM(S.TERM_OTP)/COUNT(S.TERM_OTP),4) *100 OTP_PCT
from wgubi.vw_rst_student s
where s.month_begin_Date >= '01-dec-2018'
and s.term_start_date >= '01-dec-2018'
AND S.TERM_END_DATE < SYSDATE
AND S.TERM_OTP IS NOT NULL
AND S.STUDENT_PIDM NOT IN (select student_pidm from WGUBI.INF_STUDENT_GRADS where DEGREE_status_CODE = 'AW')
AND TRUNC(S.TERM_END_DATE) = S.MONTH_END_dATE
GROUP BY s.TERM_START_DATE
, s.TERM_END_DATE
, S.MONTH_END_DATE
, S.COLLEGE_CODE
,s.LEVEL_CODE
ORDER by 1;

Using "Import Mode", I connected all the requried tables to PowerBI and joined them.

Now, Using DAX, I created a Measure for "OTP_PCT" as :

OTP_PCT  = CALCULATE(

Round(SUM(VW_RST_STUDENT[TERM_OTP])/count(VW_RST_STUDENT[TERM_OTP]),4),

filter (INF_STUDENT_GRADS,INF_STUDENT_GRADS[DEGREE_STATUS_CODE]<> "AW"),

filter(VW_RST_STUDENT, VW_RST_STUDENT[TERM_OTP] in {0,1}))

It's giving wrong results.

Kindly help me.

Also please suggest how to use Blank Query for the same.

[ananeto]

What exactly are you trying to calculate? An average?

And why do you say the results are wrong?

[v-frfei-msft]

Hi @Anonymous ,

Could you please 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.