SQL to DAX conversion

[Anonymous]

I want to convert the below query to a DAX measure-

select count(1) from [PRNTO].[vw_DIM_PRNTO_Outlet] a

where a.ctry_id=173

and rss_in=1 and hpc_busn_in='HPC' and b2b_strt_dt<'2020-02-01'

and outlet_id in

(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 ertm_type_cd='B2B'

and busn_catg_cd='HPC'

and cal_dt between '2020-02-01' and '2020-07-31'

)

Note-

vw_DIM_PRNTO_Outlet and FCT_DSTR_SEC_SLS_DATA have a 1-M relationship in the data model.@dax

[Anonymous · Accepted solution]

Hi @Anonymous ,

Something like this.

Measure =
CALCULATE (
    COUNT ( 'Table 1'[Column1] ),
    FILTER (
        'Table 1',
        conditions
        &&
        'Table 1'[Column2]
            IN CALCULATETABLE (
                VALUES ( 'Table 2'[Column1] ),
                FILTER ( 'Table 2', conditions )
            )
    )
)

Best Regards,

Jay

[amitchandak]

@Anonymous ,

a measure like

measure =
Var _out = summarize(filter( (FCT_DSTR_SEC_SLS_DATA, [Blg_Type_Cd] in {"ZRSS","ZRS1"} || [Sls_Sts_Cd] ="B2B") && [ctry_id]=173 && [ertm_type_cd]="B2B"
&& [busn_catg_cd]="HPC" && [cal_dt] >= date(2020,1,1) & [cal_dt] >= date(2020,7,31) ),[outlet_id])
return
calculate(count([outlet_id]) , filter(FCT_DSTR_SEC_SLS_DATA, [rss_in]=1 && [hpc_busn_in]="HPC" && [b2b_strt_dt]<date(2020,02,01) && outlet_id in _out))