Sql Query to DAX

[Anonymous]

Hi All,

Could somebody help me to write the below SQL statement in DAX.

No of repeat orders from existing customer 

select count(distinct ord_cd),Ord_Cust_Id from FCT_ORD_LINE ord  inner join DIM_STS STS on STS.Sts_Id= ORD.Ord_Sts_Id
AND STS.Sts_Text NOT IN ('Cancelled','Expired') where Ord_Crt_Dt between (Period) and Ord_Cust_Id in (sel Ord_Cust_Id from FCT_ORD_LINE ord inner join DIM_STS STS on STS.Sts_Id= ORD.Ord_Sts_Id AND STS.Sts_Text NOT IN ('Cancelled','Expired')
where Ord_Crt_Dt < Period ))"

Thanks & Regards,

Rajeev

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

First, you can type the SQL statement when you import data from SQL Server, type the SQL statement in the following box(highlighted in red line).




In your model, you import FCT_ORD_LINE and DIM_STS tables seperately, please follow the steps below.

1. Create a relationship between FCT_ORD_LINE and DIM_STS tables trough STS.Sts_Id= ORD.Ord_Sts_Id. 

2. Create a measure using the formula below.

measure =
CALCULATE (
    DISTINCTCOUNT ( [ord_cd] ),
    FILTER (
        DIM_STS,
        [Sts_Text}<>'Cancelled'||[Sts_Text}<>'Expired'||[Ord_Crt_Dt] < Period
    )
)

3. Create a table visual, select [Ord_Cust_Id] and [measure] as values level, you will get expected result.

Best Regards,
Angelia