Forum Discussion

dobregon's avatar
dobregon
Impactful Individual
7 years ago

Direct Query Filters in the report

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 n...
Need Help
dobregon's avatar
dobregon
Impactful Individual
7 years ago

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's avatar
v-frfei-msft
Community Support
7 years ago

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's avatar
    dobregon
    Impactful Individual
    7 years ago

    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).