Forum Discussion
SQL to DAX conversion
How to convert below SQL query to a DAX measure-
[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 , 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]))
2 Replies
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]))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