Forum Discussion

vini_udenia's avatar
vini_udenia
Helper I
3 years ago

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...
Need Help
MahyarTF's avatar
MahyarTF
Memorable Member
3 years ago

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's avatar
vini_udenia
Helper I
3 years ago

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]