Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Subqueries in DAX

Hi Team,

 

I am facing problem to write below sql subqueries in DAX

 

select count( a.member_key) from (
select a.member_key as member_key from Fact_Member_Analysis a
join Dim_Store c on a.Store_Key = c.Store_Key
join Dim_Member b on a.Member_Key = b.Member_Key
where Sales_Date_Key like '202007%' and b.Member_Key not in (-1)
and SALES_TRANSACTION_PLATFORM not like 'xyz' and b.Member_Group_Key in (9, 10, -1) and c.Store_State_Key = 5
group by a.Member_Key
having count( a.Store_Key)>=2) a

 

I have tried to write DAX but getting wrong answer.

 

store purchase = CALCULATE(
SUMX(SUMMARIZE(
Fact_Member_Analysis,Fact_Member_Analysis[Member_Key],
"Count_Store",
if(COUNT(Fact_Member_Analysis[Store_Key])>=2,1,0)),[Count_Store]),
FILTER(Dim_Member,Dim_Member[Member_Group_Key] in {9, 10, -1}
&& Dim_Member[Member_Key] <> -1),
FILTER(Fact_Member_Analysis,Fact_Member_Analysis[SALES_TRANSACTION_PLATFORM]<> "xyz")
,FILTER(Template,Template[Month_Key]=[this_month]))
 
Please help me to solve this.
 

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Try like

    SUMX(filter(SUMMARIZE(
    Fact_Member_Analysis,Fact_Member_Analysis[Member_Key],
    "Count_Store",
    calculate(COUNT(Fact_Member_Analysis[Store_Key]),FILTER(Dim_Member,Dim_Member[Member_Group_Key] in {9, 10, -1}
    && Dim_Member[Member_Key] <> -1),
    FILTER(Fact_Member_Analysis,Fact_Member_Analysis[SALES_TRANSACTION_PLATFORM]<> "xyz")
    ,FILTER(Template,Template[Month_Key]=[this_month]))),[Count_Store] >2),[Count_Store]
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks amitchandak ,

       

      Its giving the same result. Not the correct answer which I am getting from sql.