Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.
 
1 ACCEPTED SOLUTION

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

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@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
Not applicable

Thanks @amitchandak ,

 

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

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

Anonymous
Not applicable

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

Your solution works for me. Thanks

Anonymous
Not applicable

@amitchandak ,

 

No Amit, I cannot share the data

@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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.