Query to filter

[wainnoce]

i am currently trying to create a report that is pulling from several tables. there is a very specific filter we are trying to use to compare last 12 months of data to the 12 months before that. I was curious as if there was a way to implement a query we have created to do this or if I would have to create a table in our databse and then do a live query on that.

[Anonymous]

You can use DAX to create appropriate year-over-year measures. There is anice repository of DAX patterns on SQLBI.com website: http://www.daxpatterns.com/time-patterns/.

[wainnoce]

At this point I am trying to work all within Power BI before using anyhting external. I just want to be able to filter my data by date which I may be able to work with what Power BI has provided but I wanna have the option to show only the top 25 customers by a certain field such as weight.

[Anonymous]

I think we need a bit more information on what you're actually trying to do.

If you're wanting to use SQL that you've already created, you can expand the "Advanced options" section of the database prompt.

If you're trying to filter by top 25, you can use the "Top N" feature in the report filters, or even get the top 25 with raw SQL or DAX in your queries. Same goes for date filtering: you can implement it either in SQL, DAX, or with the visualization filters.

[wainnoce]

select cc.name2||'('||cc.code||')' comp,
nvl(co.name,c.name) customer_group,
g.name||' '||g.city||', '||g.state_abbr||' ('||g.code||')' generator,
td.site_name||' - '||td.site_city||', '||td.site_state_abbr tsd,
w.common_name,
to_char(pickup_date,'MON-YYYY') pickup_month,
(case when pickup_date between add_months(to_date('01-'||to_char(sysdate,'mon-yyyy')),-12) and last_day(add_months(sysdate,-1)) then 'LTM' else 'PTM' end) period,
sum(t.lbs)/2000 tons
from transaction t, customer c, corporate co, generator g, wastestream w, tsd td,
consolidated_company_division d, consolidated_company cc
where t.customer_code=c.code
and c.corporate_code=co.code(+)
and t.generator_code=g.code
and t.wastestream_code=w.code
and t.generator_code=w.generator_code
and t.tsd_code=td.code
and t.pickup_date between add_months(to_date('01-'||to_char(sysdate,'mon-yyyy')),-24) and last_day(add_months(sysdate,-1))
and t.statcode_code between 50 and 80
and cc.code != 58
and t.company_code=d.company_code
and d.consolidated_company_code=cc.code
group by cc.name2||'('||cc.code||')' ,
nvl(co.name,c.name) ,
g.name||' '||g.city||', '||g.state_abbr||' ('||g.code||')' ,
td.site_name||' - '||td.site_city||', '||td.site_state_abbr ,
p.last_name||', '||p.first_name ,
decode(p2.last_name,'','',p2.last_name||', '||p2.first_name) ,
w.common_name,
to_char(pickup_date,'MON-YYYY') ,
(case when pickup_date between add_months(to_date('01-'||to_char(sysdate,'mon-yyyy')),-12) and last_day(add_months(sysdate,-1)) then 'LTM' else 'PTM' end);

I have this SQL statement and am trying to use this newly created table. Are you saying there is a way to do a live query into our database to pull this information?

[v-huizhn-msft · Accepted solution]

Hi @wainnoce,

When you get data from SQL Server database, you can type the SQL in the following Advanced option(see the highlighted zone in black line), you can choose use Import or Direct Query mode. If you use the Direct Query mode, there are some limitations, more details, please see the following article.

Thanks,
Angelia