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

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

Hi dobregon,

 

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

 

 

Regards,

Frank

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