sql into dax
1 Topic
Converting SQL statement into DAX calculated table
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.Solved717Views0likes2Comments