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
