SQL to DAX conversion

[Anonymous]

How to convert below SQL query to a DAX measure-

select outlet_id from
[SDDW].[FCT_DSTR_SEC_SLS_DATA]
where ( [Blg_Type_Cd] in ('ZRSS','ZRS1') or [Sls_Sts_Cd] ='B2B')
and ctry_id=173
and cal_dt between '2019-03-01' and '2019-03-31'
and busn_catg_cd='HPC'
intersect
select outlet_id from
[SDDW].[FCT_DSTR_SEC_SLS_DATA]
where ( [Blg_Type_Cd] in ('ZRSS','ZRS1') or [Sls_Sts_Cd] ='B2B')
and ctry_id=173
and busn_catg_cd='HPC'
and cal_dt between '2019-02-01' and '2019-02-28'

[Anonymous]

Hi @Anonymous,

I'd like to suggest you take a look at how SQL string compare achieved in dax, you can write a calculate function with filters to simulate t-SQL query:

From SQL to DAX: String Comparison 

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@Anonymous , You will get a new table

https://docs.microsoft.com/en-us/dax/intersect-function-dax

Check do you need except https://docs.microsoft.com/en-us/dax/except-function-dax

if so replace intersect with except

intersect( summarize(filter( (FCT_DSTR_SEC_SLS_DATA, [Blg_Type_Cd] in {"ZRSS","ZRS1"} || [Sls_Sts_Cd] ="B2B") && [ctry_id]=173 && [Sls_Sts_Cd] ="B2B"
&& [busn_catg_cd]="HPC" && [cal_dt] >= date(2019,3,1) & [cal_dt] >= date(2019,3,31) ),[outlet_id])
,
summarize(filter( (FCT_DSTR_SEC_SLS_DATA, [Blg_Type_Cd] in {"ZRSS","ZRS1"} || [Sls_Sts_Cd] ="B2B") && [ctry_id]=173 && [Sls_Sts_Cd] ="B2B"
&& [busn_catg_cd]="HPC" && [cal_dt] >= date(2019,2,1) & [cal_dt] >= date(2019,2,28) ),[outlet_id]))