Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Hi @sgpwrbi
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
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |