Direct Query Filters in the report

[dobregon]

Hi,

I have done a direct query into my database as an example

SELECT
  Element,
  TypeElement,
  Date,
  Cost
From ElementCost

 This "table" has a lot of information for years so it is not possible to do an import query becasue the system will need 4h to take all the information in the table, for that reason i'm thinking to use a direct query becasue in this type of query the system will take only the information filtered so, the system shouldn't take long time to take the info.

So for that, I have done 2 slicers:

1. slicer for dates between in order to select only a period of time
2. Slicer to select the TypeElement

The problem is that when I select the dates or the typeelemtn the PowerBI run the query and I dont want this. I know that exist the apply buttom but it is individualy for each slicer so... it is not a real solution for me. 

I'm looking for a apply buttom or something that I can select the period of tyme in slicer 1 and the Typeelement in slicer 2 and then apply the filters and run the query in order to do everthing in one time.

Is it possible?

[Thejeswar]

@dobregon,

Yes you can import the data by restricting for a certain period of time.

Incase if that is not possible, spend 4h to import it for the first time, but then implement Incremental Data Refresh to restrict the amount of data being imported.

The Query whoch importing the data should be like how @v-frfei-msft has specified

In case if this is not possible, try concatenating the two columns "something like says 2014 - TypeElementValue"

Now filtering this after enabling an Apply button, should send the query only once to the report

[dobregon]

Thanks for the reply @Thejeswar 

The incremental data refresh is a option (as I know, maybe i'm wrong) only for the premium users and for now i'm only a Pro user so, i can't do that.

And i Can't concatenate the slicer because I have thousand of type elements and various years/month/days so for that i'm asking for an only 1 apply buttom becasue i see that i can put buttoms but they can't do an action to apply the slicers.

Other thing that I'm thinking now is to do a SP with 3 paramaters (startdate, enddate, typeelement) and then write the 3 parameters and apply but i can't find how can select the parameter in the powerbi.app web when I publish the report.

I see that they can writhe parameters in function of a query but I have tried to do that and i can't select a query. Is there any option?

[v-frfei-msft]

Hi @dobregon,

Why did you not use and in the query? 

let
    Source = Sql.Database("localhost", "Power_BI_Test", [Query="select * from Financial_1 where ID>50 and Year >= 2014"])
in
    Source

Here I can get the result that filterd by ID and Year at one time.

Regards,

Frank

[dobregon]

thanks for the reply @v-frfei-msft but this is not a solution because the stakeholders of the report want to have a dynamic filter in order to see a period of time and type of element so I'cant use in the query.

The problem is that my database has a lot of rows and if i do a import query the system needs more than 7h to upload all the data and we need to do every day so, the import query can't do that.

The directy query seems to be a good solution becasue if the people needs to filter Period of time and type element and then wait 10-30seg it is a good solution but the problem is if i have 2 slicers the system will run the direct query when the people change any slicer instead of wait to finish the selection of the 2 slicers and the run the query. For that i'm asking if they could be a bottom to apply both filters at the same time.

[v-frfei-msft]

Hi @dobregon,

You can use Filter in the Field to filter more than one columns at one time.

 

Regards,

Frank

[dobregon]

hi @v-frfei-msft i know this option but it is similar to filter using slicers (and connecting them in all the pages). the problem is that I need filter "dates" (with start and end) and types, so in this case i need to have 2 filters (same than slicers) but in this option i need to click "apply" individually so, i'm in the same problem.

I'm trying to look how to do using a SP with parameters but i can't find a solution yet

[v-frfei-msft]

Hi @dobregon,

Then we can use parameters in power query to work around.

Here is the M code for your reference.

(para as text, para1 as date,para2 as date) as table =>
let
    Source = Sql.Database("localhost", "Power_BI_Test"),
    dbo_Financial_1 = Source{[Schema="dbo",Item="Financial_1"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_Financial_1, each [Date] < para1 and [Date] > para2),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each [ID]> para)
in
    #"Filtered Rows2"

Regards,

Frank

[dobregon]

Hi, 

I can't do that becasue I need that the parameters&filters were dynamic and in the powerbi app the final users couln't change the parameters.

I have done 2 things and it seems to work (but not much faster):

- A direct query to all the table

- Import query about all the types of elements(bikes, cars, bus, etc) that I have.

I have done a relationship between both querys using one type element to many type elements in the direct query.

Then in the visuals i have created an slicer with the type of elements that i have in the import query and another visual with the details, so when i click in the first visual the system filters for that element and then run the direct query for this element (it takes 15-20seg).