Sub Query - How to write DAX or handle in Power Query

[vini_udenia]

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]

[MahyarTF]

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

[vini_udenia]

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]