Forum Discussion

Adarshkr's avatar
Adarshkr
New Member
4 years ago

Need help in writing Dax for below condition(SQL)

Hi Members, 

Please help in converting below SQL query to Dax 

select count(distinct(agent_SK)) from edw.FactEnrolledOpportunityClass f
join edw.DimOpportunityStatus d on f.Enrolled_Opportunity_Status_SK=d.Opportunity_Status_SK
where Opportunity_Status_Name='passed' and year(f.Enrolled_Opportunity_Status_Date_ET)>=2020
and agent_SK in (
select distinct agent_SK from edw.FactEnrolledOpportunityClass f
join edw.DimOpportunityStatus d on f.Enrolled_Opportunity_Status_SK=d.Opportunity_Status_SK
where Opportunity_Status_Name='Enrolled(Full Payment)'
and year(f.Enrolled_Opportunity_Status_Date_ET)=2020
)

i have tried to create the DAX as below but the count seems to be not matching 

below is my DAX query :

agents passed filters =


var A=SELECTCOLUMNS(CALCULATETABLE(SUMMARIZE(Fact_Enrolled_Opportunity_Class,Fact_Enrolled_Opportunity_Class[Agent SK]),(Dim_Opportunity_Status[Opportunity Status Name]) = "Enrolled(Full Payment)",USERELATIONSHIP(Fact_Enrolled_Opportunity_class[Enrolled_Opportunity_Status_Date_ET_SK],Dim_Date[Date SK])),"Agent_SK",[Agent SK])

var _date = calculate(min(Fact_Enrolled_Opportunity_class[Enrolled_Opportunity_Status_Date_ET_SK]), Dim_Opportunity_Status[Opportunity Status Name] = "Enrolled(Full Payment)")

var B=SELECTCOLUMNS(CALCULATETABLE(SUMMARIZE(Fact_Enrolled_Opportunity_Class,Fact_Enrolled_Opportunity_Class[Agent SK]),(Dim_Opportunity_Status[Opportunity Status Name]) = "Passed",Fact_Enrolled_Opportunity_class[Enrolled_Opportunity_Status_Date_ET_SK]>=_date),"Agent_SK",[Agent SK])

var joint_count=NATURALINNERJOIN(A,B)

return countrows(distinct((joint_count))

I request the active members to help me out in ammeding the queries. Thanks in Advance



Need Help

1 Reply