Forum Discussion
Sub Query - How to write DAX or handle in Power Query
I have the requirement to exclude certain schools from a measure calcuation. I have been able to define the logic in a sub-query, not sure how can this be converted to a DAX.
SELECT DISTINCT SE.[YEAR], SE.[MONTH], SC.[CD_SCH], COUNT (F.[SK_CAND]) #Registration
FROM [dbo].[TBL_DM_FCT_SUB_COMP] F, [dbo].[TBL_DM_DIM_SCH] SC,[dbo].[TBL_DM_DIM_SESS] SE, [dbo].[TBL_DM_DIM_SUB] SU
WHERE F.[SK_SCH] = SC.[SK_SCH]
AND F.[SK_SESS] = SE.[SK_SESS]
AND F.[SK_SUB] = SU.[SK_SUB]
AND SU.[TXT_SUB] like '%PERS P%'
AND F.[WITHDRAWN_IND] = 'N'
-- Excludes any schools found by the code below
AND not exists
( SELECT DISTINCT SC2.[CD_SCH]
FROM [dbo].[TBL_DM_FCT_SUB_COMP] F2, [dbo].[TBL_DM_DIM_SCH] SC2,[dbo].[TBL_DM_DIM_SESS] SE2, [dbo].[TBL_DM_DIM_SUB] SU2
WHERE F2.[SK_SCH] = SC2.[SK_SCH]
AND F2.[SK_SESS] = SE2.[SK_SESS]
AND F2.[SK_SUB] = SU2.[SK_SUB]
AND SU2.[TXT_SUB] not like '%PERS P%' -- Subject registrations which aren’t personal project
AND F2.[WITHDRAWN_IND] = 'N'
AND (F2.[SK_SESS] = F.[SK_SESS] or F2.[SK_SESS] = F.[SK_SESS] -1 ) -- Where it is the same session as selected in the first part of the statement or is the previous session.
AND SC2.[CD_SCH] = SC.[CD_SCH]
GROUP BY SC2.[CD_SCH]
)
GROUP BY SE.[YEAR], SE.[MONTH], SC.[CD_SCH]
2 Replies
Hi,
From my point of view, it will be a bit complicated in Dax, but I usually create a separate query for the excluded part, then create a separate table as a Left Anti Join option :
1- Table A
2- Table B
3- Merge table :
Appreciate your Kudos and please make it as a solution if it helps
Thank you for your advise, the Left Anti Join definitely solves part of the requirement.
The 2nd part in which the subquery need to join based on the SK_SESS and SK_SESS -1 of the main query is still quite puzzling for me.Highlighted the portion:
SELECT DISTINCT SE.[YEAR], SE.[MONTH], SC.[CD_SCH], COUNT (F.[SK_CAND]) #Registration
FROM [dbo].[TBL_DM_FCT_SUB_COMP] F, [dbo].[TBL_DM_DIM_SCH] SC,[dbo].[TBL_DM_DIM_SESS] SE, [dbo].[TBL_DM_DIM_SUB] SU
WHERE F.[SK_SCH] = SC.[SK_SCH]
AND F.[SK_SESS] = SE.[SK_SESS]
AND F.[SK_SUB] = SU.[SK_SUB]
AND SU.[TXT_SUB] like '%PERS P%'
AND F.[WITHDRAWN_IND] = 'N'
-- Excludes any schools found by the code below
AND not exists
( SELECT DISTINCT SC2.[CD_SCH]
FROM [dbo].[TBL_DM_FCT_SUB_COMP] F2, [dbo].[TBL_DM_DIM_SCH] SC2,[dbo].[TBL_DM_DIM_SESS] SE2, [dbo].[TBL_DM_DIM_SUB] SU2
WHERE F2.[SK_SCH] = SC2.[SK_SCH]
AND F2.[SK_SESS] = SE2.[SK_SESS]
AND F2.[SK_SUB] = SU2.[SK_SUB]
AND SU2.[TXT_SUB] not like '%PERS P%' -- Subject registrations which aren’t personal project
AND F2.[WITHDRAWN_IND] = 'N'
AND (F2.[SK_SESS] = F.[SK_SESS] or F2.[SK_SESS] = F.[SK_SESS] -1 ) -- Where it is the same session as selected in the first part of the statement or is the previous session.
AND SC2.[CD_SCH] = SC.[CD_SCH]
GROUP BY SC2.[CD_SCH]
)
GROUP BY SE.[YEAR], SE.[MONTH], SC.[CD_SCH]
