SQL to DAX conversion

[Anonymous]

How to convert below SQL query to a DAX measure. The 'date' case structure should be dymanic to get last 4 months data.

select  mnth,M1_Ord,M2_Ord,M3_Ord,M4_Ord,Is_cons_flg,count(outlet_id)  outlet_bucket from
(

select mnth,outlet_id, dstr_id,dstr_site_cd,M1_Ord,M2_Ord,M3_Ord,M4_Ord,

(case when M1_ord<= M2_Ord and M2_Ord<=M3_ord and M3_ord<=M4_ord then 'Y'
else 'N' end )Is_cons_flg
from (
select mnth,outlet_id,dstr_id,dstr_site_cd,(case when mnth='201901' then ord else 0  end )M1_Ord,
(case when mnth='201902' then ord else 0 end ) M2_Ord,
(case when mnth='201903' then ord else 0 end ) M3_Ord,
(case when mnth='201904' then ord else 0 end ) M4_Ord
from (
select convert(varchar(6),cal_dt,112) mnth,outlet_id,dstr_id,dstr_site_cd,count(distinct bill_doc_num) ord
from [SDDW].[FCT_DSTR_SEC_SLS_DATA]
where cal_dt between '2019-01-01' and '2019-04-30'
and ertm_type_cd='B2B'
and ctry_id=173
group by convert(varchar(6),cal_dt,112),outlet_id,dstr_id,dstr_site_cd
)freq
) cnst

) vv 

group by mnth,M1_Ord,M2_Ord,M3_Ord,M4_Ord,Is_cons_flg

[v-rzhou-msft]

Hi @Anonymous 

Your SQL language is complex. Could you provide me with your data model and a screenshot of the result you want?  And this will make it easier for me to understand your require.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[mwegener]

Hi @Anonymous ,

a 1:1 translation makes no sense in my opinion.

You can filter and transform the data with Power Query while importing data.

In the report you can then visualize the data in a matrix.

The last 4 months can be filtered using a relative date filter.

[Anonymous]

Its a live connection. Can't do any changes in Power query. Need a DAX measure for same.

[mwegener]

Hi @Anonymous ,

can you share a screenshot of the table from the Power BI model view?