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.
I have two queries on same datasource/database in Oracle.
1) Returns sales data by sources on a timeline
select (CASE WHEN x.SOURCE='Online-Brand' THEN 1 WHEN x.SOURCE='Online-Dealer' THEN 2 WHEN (x.SOURCE='Parts' or x.SOURCE='Sales' or x.SOURCE='Stock') THEN 3 ELSE null END) AS SOURCE, CAST(TRUNC(x.createdon) AS DATE) AS ORDER_DATE, TRUNC(to_date(to_char(x.createdon,'MM/DD/YYYY'),'MM/DD/YYYY'), 'IW')-1 AS WEEK_START_DATE, (TOTALPRODUCT +TOTALTAX + TOTALADJUSTMENT +TOTALSHIPPING +TOTALTAXSHIPPING) AS TOTAL FROM ecom_acc.orders o, ecom_acc.xorders x where o.status not in ('P','X') and o.orgentity_id != '7000000000000001703' and x.status not in ('NEW', 'INC', 'CAN', 'RET') and o.orders_id = x.orders_id and CAST(TRUNC(x.createdon) AS DATE) >='01-SEP-17'
2) Returns total sales by a product
select unique a.catentry_id, b.name, sum(a.totalproduct) from ecom_acc.orderitems a, ecom_acc.catentdesc b, ecom_acc.xorders c where a.catentry_id = b.catentry_id and b.language_id=-1 and a.orders_id=c.orders_id and a.status not in('P','X') and c.source='Online-Brand' and a.LASTCREATE >= to_date('01-09-2017 07:00:00', 'DD-MM-YYYY HH24:MI:SS') group by a.catentry_id,b.name order by sum(a.totalproduct) desc
I want report data from 2nd query changed based on timeline I select on report from 1st query. If I select Jan, 2018 in first report, I want 2nd report to show product wise sales for Jan, 2018. Any idea how I can achieve it?
The dashboard with two reports looks like this.
Hi @vopatel,
Based on your queries, even they are using the same datasource, they don't have relationships (Related columns). So with current structure, I'm afraid it is not possible to make interaction between these two queries.
And to achieve your requirement, you need to create related columns in the two datasets first, then create relationship between the two datasets. Something like below sample:
Thanks,
Xi Jin.
To create relationships, you need
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |