Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |