Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

SQL to DAX conversion

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_...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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]))

amitchandak's avatar
amitchandak
Super User
5 years ago

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]))