Forum Discussion

PowerBIET's avatar
PowerBIET
Frequent Visitor
3 years ago
Solved

Convert Oracle SQL Query to DAX

Expect Output - first_shoppers = 3083

Expect Output - avg_spend_first_d = 134.53

Adding Sample Data:

report_datechannelmembership_numdemand_revenue
3/1/2023OPIC6923297473.89
12/1/2022OPIC692329795.57
3/1/2023OPIC4732483220.59
3/1/2023OPIC796879954.09
3/1/2023OPIC770789199.94
3/1/2023OPIC295379767.22
3/1/2023OPIC2119246173.53
3/1/2023OPIC2159933159.96
3/1/2023OPIC2276172260.8
3/1/2023OPIC554957599.13
3/1/2023OPIC210926476.79
3/1/2023OPIC9386426126.91
3/1/2023OPIC679898082.36
3/1/2023OPIC4165529151.66
3/1/2023OPIC9276831298.21
3/1/2023OPIC4771072106.87
11/1/2022OPIC4771072304.89
3/1/2023OPIC3956433153.84
3/1/2023OPIC1650323100.91
3/1/2023OPIC997608576.52
3/1/2023OPIC1061082115.94

 

Full Data

https://docs.google.com/spreadsheets/d/1iiuPFVPFpS7ojtMRlEaRxQPKpR8I-8g8/edit?usp=sharing&ouid=11351... 

 

We have to convert this ORACLE SQL(below) query from a legacy system into dax.  Each report_date is dynamnic in the legacy system.   

 

This query gets first time shoppers that only placed one order is a specified range, and who have not placed any order 6 month prior starting from start day, hence the MINUS 180 part. 

How can I rewrite the query in DAX the includes the MINUS section of -180 that excludes shoppers who placed an order in Power BI?

 

Here is the query:

SELECT
    NVL(SUM(mem_count),0) AS first_shoppers,
    CASE WHEN SUM(revenue_d) > 0 AND SUM(mem_count) > 0 THEN SUM(revenue_d)/SUM(mem_count) ELSE 0 END avg_spend_first_d
        FROM
        ( SELECT
            COUNT( DISTINCT mbr.membership_num) AS mem_count    ,
            SUM(demand_revenue)                     AS revenue_d
                FROM product pd,
                ( SELECT membership_num FROM
                        ( SELECT membership_num, COUNT(DISTINCT order_num) AS order_count
                            FROM product
                                WHERE report_date BETWEEN '2023-03-01' AND '2023-03-02'--dynamic
                                AND channel = 'OPIC'
                                AND membership_num IS NOT NULL
                            GROUP BY membership_num
                            HAVING COUNT(DISTINCT order_num) = 1
                        )
                        MINUS
                        SELECT DISTINCT membership_num
                            FROM product
                                WHERE report_date < '2023-03-01' AND report_date >= '2023-03-02'-180 --dynamic
                                AND channel = 'OPIC'
                                AND membership_num IS NOT NULL
                ) mbr
                WHERE pd.membership_num = mbr.membership_num
                AND pd.channel = 'OPIC'
                AND report_date BETWEEN '2023-03-01' AND '2023-03-02' --dynamic
        );
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    PowerBIET Should be something along these lines. See PBIX attached below signature. Hard to test because you only have a months worth of data.

    First Shoppers = 
    VAR __DaysAgo = 180
    VAR __MinRange = MIN('Table'[report_date])
    VAR __TableRange1 = SUMMARIZE('Table',[membership_num],"__Count",COUNTROWS('Table'))
    VAR __TableRange2 = FILTER(__TableRange1, [__Count] = 1)
    VAR __PastRange = FILTER(ALL('Table'),[report_date] < __MinRange && [report_date] >= __MinRange - 180)
    VAR __FirstShoppers = DISTINCT(SELECTCOLUMNS(__TableRange2,"__MemberNum",[membership_num]))
    VAR __PastShoppers = DISTINCT(SELECTCOLUMNS(__PastRange,"__MemeberNum",[membership_num]))
    VAR __MembersOfInterest = 
        EXCEPT(
            __FirstShoppers,
            __PastShoppers
        )
    VAR __Result = COUNTROWS(__MembersOfInterest)
RETURN
   __Result

     

    Average Spend = 
    VAR __DaysAgo = 180
    VAR __MinRange = MIN('Table'[report_date])
    VAR __TableRange1 = SUMMARIZE('Table',[membership_num],"__Count",COUNTROWS('Table'))
    VAR __TableRange2 = FILTER(__TableRange1, [__Count] = 1)
    VAR __PastRange = FILTER(ALL('Table'),[report_date] < __MinRange && [report_date] >= __MinRange - 180)
    VAR __FirstShoppers = DISTINCT(SELECTCOLUMNS(__TableRange2,"__MemberNum",[membership_num]))
    VAR __PastShoppers = DISTINCT(SELECTCOLUMNS(__PastRange,"__MemeberNum",[membership_num]))
    VAR __MembersOfInterest = 
        EXCEPT(
            __FirstShoppers,
            __PastShoppers
        )
    VAR __Result = AVERAGEX(FILTER('Table', [membership_num] IN __MembersOfInterest),[demand_revenue])
RETURN
    __Result

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    PowerBIET Should be something along these lines. See PBIX attached below signature. Hard to test because you only have a months worth of data.

    First Shoppers = 
    VAR __DaysAgo = 180
    VAR __MinRange = MIN('Table'[report_date])
    VAR __TableRange1 = SUMMARIZE('Table',[membership_num],"__Count",COUNTROWS('Table'))
    VAR __TableRange2 = FILTER(__TableRange1, [__Count] = 1)
    VAR __PastRange = FILTER(ALL('Table'),[report_date] < __MinRange && [report_date] >= __MinRange - 180)
    VAR __FirstShoppers = DISTINCT(SELECTCOLUMNS(__TableRange2,"__MemberNum",[membership_num]))
    VAR __PastShoppers = DISTINCT(SELECTCOLUMNS(__PastRange,"__MemeberNum",[membership_num]))
    VAR __MembersOfInterest = 
        EXCEPT(
            __FirstShoppers,
            __PastShoppers
        )
    VAR __Result = COUNTROWS(__MembersOfInterest)
RETURN
   __Result

     

    Average Spend = 
    VAR __DaysAgo = 180
    VAR __MinRange = MIN('Table'[report_date])
    VAR __TableRange1 = SUMMARIZE('Table',[membership_num],"__Count",COUNTROWS('Table'))
    VAR __TableRange2 = FILTER(__TableRange1, [__Count] = 1)
    VAR __PastRange = FILTER(ALL('Table'),[report_date] < __MinRange && [report_date] >= __MinRange - 180)
    VAR __FirstShoppers = DISTINCT(SELECTCOLUMNS(__TableRange2,"__MemberNum",[membership_num]))
    VAR __PastShoppers = DISTINCT(SELECTCOLUMNS(__PastRange,"__MemeberNum",[membership_num]))
    VAR __MembersOfInterest = 
        EXCEPT(
            __FirstShoppers,
            __PastShoppers
        )
    VAR __Result = AVERAGEX(FILTER('Table', [membership_num] IN __MembersOfInterest),[demand_revenue])
RETURN
    __Result
    • PowerBIET's avatar
      PowerBIET
      Frequent Visitor
      3 years ago
      Thanks, this below ended up being my final code, after adding a relative date slicer.
      Shoppers =
       
          VAR __MinRange = CALCULATE( MIN(Cal[Date]) )
          VAR __MaxRange = CALCULATE( max(Cal[Date]) )

       

          VAR __TableRange1 =  SUMMARIZE('Table',[membership_num],"__Count", DISTINCTCOUNT('Table'[order_num]) )

       

          var a  =  CALCULATETABLE( VALUES('Table'[membership_num]), FILTER(__TableRange1,[__Count]=1) )
         
          var b = CALCULATETABLE( VALUES('Table'[membership_num]),
                                 FILTER(ALL('Cal'),[Date] >= __MaxRange - 180 && [Date] < __MinRange ) )
         
          VAR __MembersOfInterest =
              EXCEPT(
                  a,
                  b
              )
          VAR __Result = COUNTROWS(__MembersOfInterest)
         

       

      RETURN
         __Result