Subqueries in DAX

[Anonymous]

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.

[amitchandak]

@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]

Thanks @amitchandak ,

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

[amitchandak · Accepted solution]

@romakhatua , Can you share sample data and sample output in table format?

[Anonymous]

@amitchandak ,Actually there was an issue with the filter while pulling from data source.

Your solution works for me. Thanks

[Anonymous]

@amitchandak ,

No Amit, I cannot share the data

[edhans]

@Anonymous can you share sample data ? If not, we are guessing. Please share sample data and the expected result.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[parry2k]

@Anonymous this is weird that you are looking for help from the community but not ready to share sample data. How do you expect help if you can't help us? Why not you slap some sample dummy data and share it? You need to help us to get help otherwise nobody has time to do the guesswork and solve your issue.

Good luck!