How to model this SQL Statement in PowerBI without using SQL?

[Anonymous]

Hi all

is it possible to model following SQL statement in PowerBI without using any SQL? The tables already exist in PowerBI.

I also want order_type and purch_date and customer_type to be used as slicers in PowerBI (red coloured sql statements).

Goal is to provide a customer list with aggregated sales values based on parameters of order_type, purch_date and cust_type. How would you solve it? What is best way to do it? 

select cust_id, cust_name, cust_address,
       nvl(purches,0) as no_of_purches
       nvl(amount,0) as purch_amount
  from customer_base_table c --one row per customer
       left join
       (

       select cust_id,
              count(distinct purch_id) as purches
              sum(amount) as amount
         from purchase_fact_table --zero or multiple rows per customer
        where order_type = 'xyz'
          and purch_date between '2018-10-12' and '2018-12-05'
        group by cust_id) f
    on c.cust_id = f.cust_id

 where c.cust_type = 'A';

Any help on this is kindly appreciated...

Regards, Simon

[Anonymous]

Hi @Anonymous

Please upload sample data and the pbix file to OneDrive or Google Drive and share the link here. Please also provide the output expected.

Cheers

CheeenuSing