Converting SQL statement into DAX calculated table

[alaskanko]

Hi all,

I am fairly new to DAX (only few weeks in) and trying to figure out how to convert the below SQL statement into a calculated table in DAX:

SELECT qry_DMT_DATA.ID
FROM
(SELECT qry_DMT_DATA.[Establishment Name],qry_DMT_DATA.LRN ,MAX(qry_DMT_DATA.[Learning Actual End Date]) AS MaxAED
FROM qry_DMT_DATA
WHERE (qry_DMT_DATA.[Leaner completion status]='Withdrawn' OR
qry_DMT_DATA.[Leaner completion status]='Temporarily withdrawn') AND qry_DMT_DATA.[Withdrawal reason]='Health Issues'
GROUP BY [LRN],[Establishment Name]) AS qry_HealthIssues INNER JOIN qry_DMT_DATA ON qry_HealthIssues.[LRN]=qry_DMT_DATA.LRN AND qry_HealthIssues.[Establishment Name]=qry_DMT_DATA.[Establishment Name]
WHERE qry_DMT_DATA.[Leaner completion status]='Continuing'
AND qry_DMT_DATA.[Learning Start Date]<=MaxAED;

I have managed to produce the subquery in DAX to return me the table of those withdrawn with their latest AED, but I am struggling to link it back to the main table to retrieve the ID for all those which are still continuing.

I know there must be a simple solution but somehow I have hit a wall and cannot figure it out.

Went through a lot of websites to help me work it out but to I am stuck and cannot move forward.

I will be grateful if anyone can point me to the right direction how to create a calculated table in DAX using the above parameters.

One of the solutions is to do all this in the SQL database and simply pull the results into the Power BI but this is more of a learning project than simple solution seeking for me.

Many thanks for your time and effort.

[lbendlin · Accepted solution]

trying to figure out how to convert the below SQL statement into a calculated table in DAX

Please rethink your approach.  State the business problem you are trying to solve and we can advise how to do that in Power BI.  Trying to convert from one system to another is often unproductive.

[alaskanko]

Thank you for your input. 

I will rethink this and repost a new example.

I am not necessarily trying to convert solution from one system to another, it is just that I know how to do it in the SQL so thought that would allow user to see what I was trying to achieve. 

I really appreciate your help 👍