Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

SQL to DAX conversion

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
Need Help

4 Replies

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • mwegener's avatar
        mwegener
        Icon for Most Valuable Professional rankMost Valuable Professional
        5 years ago

        Hi Anonymous ,

         

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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.