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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.


Help to convert subquery to DAX

Hello, I need help converting this SQL into a DAX measure.
The sql looks for purchases that are customer recovery, that is, a customer who made a purchase, his contract expired, and after a while he returned to being a customer.




Attached is one option to do that.  Make sure the filter changes are acceptable.

View solution in original post

Super User
Super User

Don't try to "fight the API".  There is no meaningful translator from SQL to DAX. Please explain your business issue and provide sample data and expected outcome.



I didn't want a translator from SQL to DAX, in the literal sense. I wanted some help to know how to write this SQL in a way that get the same results in Power BI.

This is a dashboard that I'm developing at my job, so I don't think I can share the data.

The company I work for is a site where a teacher can generate tests. He buys credits. Each credit entitles him to enter a question on an exam. When the teacher makes a purchase he has a deadline to use the credits he bought.

I'll try to explain this SQL:
- I look for all purchases of each customer
- For each line (purchase) returned in the SQL, the subquery searches for the last purchase prior to this one and returns the expiration date of that purchase

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Please show the expected outcome based on the sample data you provided.

Sorry for the late reply and the misunderstanding. Here the link with the sample data:

The expected result is 171 orders.


Attached is one option to do that.  Make sure the filter changes are acceptable.

It worked out. I had to make some changes, but it helped me a lot. Thank you very much!

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.