convert T-SQL in DAX - self join table RRS feed

  • Question

  • Hi
    I have simple problem with inner join in Power BI Desktop. I want to convert my T-SQL in DAX (PowerBI) but I can’t found a solution
    My objective is to obtain “how many clients (ID) of 3 products buy again one specific product in the next 12 month on historical data”
    My T-SQL is:

    select COUNT(distinct b.id) 
    from sales a 
    inner join sales b on a.id=b.id and b.date_purchase>a.date_purchase 
    where (a.product in ('2', '3', '4') and b.date_purchase='30/03/2018' ) 
    and b.product='4' 
    and b.date_purchase<=DATEADD(yy, 1, a.date_purchase )

    Could you help me?
    Thursday, December 5, 2019 10:41 AM