How do you stop an entire dataset query while trying to apply a param or filter..?

[rgouette]

ok, I'm sure I'm just missing something here..

*The goal: create a report that offers a search(Dropdown w/Search box) on an ID#

*Database: SQL with several million rows

*The problem: no matter what method I employ to try & limit the returns(TOP 1000, parameters, filter on ID#  etc)upon report opening, ...the report always tries to apply the new settings, and when it trying to apply the new setting, it has to query the entire dataset..and that  takes forever..

How the heck do I put in place, a parameter, etc, without the DB having to query every row to make that change?

In the report Global Options, I've set Type Detection and Background Data to "Never..."

Under "Data Load", I've unchecked "Autodetect Relationships", and "Import Relationships from data sources..."

Hope this isn't too confusing..
Thanks for any help,

~Rich

[Nabha-Ahmed · Accepted solution]

Hi @kushanNa 

The main issue is that Power BI queries the source to populate dropdowns or parameters. To avoid scanning millions of rows:

1. Use a small parameter/disconnected table for your dropdowns (Top 1000 IDs, recent IDs, etc.).

2. Create a filtered SQL view that limits rows before Power BI connects.

3. If possible, use Incremental Refresh to reduce queries on report load.

4. Avoid pointing slicers directly at the full table—always use a smaller supporting table.

 This approach ensures the report loads quickly without querying the entire dataset.

Find this helpful? Give a Kudo • Mark as Solution – help others too!

[Royel · Accepted solution]

FYI, 

Source = Sql.Database("DESKTOP-BV0J2NP\SQLEXPRESS", "ContosoRetailDW")
Here
Server: DESKTOP-BV0J2NP\SQLEXPRESS

Database: ContosoRetailDW
you need to replace this with your information, when you complete the steps you will get edit alert. Click there and configure with your email address or user name and password. 

[v-saisrao-msft]

Hi @rgouette,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[rgouette · Accepted solution]

I have not still fully been able to accomplish my goal, but I am going to keep at it. I've gotten several good ideas and suggestions, and I think this should be closed, as it's gone on for a good while. 

[rgouette]

I am back on the issue now, I will let folks know what I find.
I do believe this is at the heart of my issue: "The main issue is that Power BI queries the source to populate dropdowns or parameters. "
So, I'll try & circumvent that ..
@Royel To answer your question, the query in SSMS takes:   10:11 to complete, and yields 11,111,344 rows

Rich

[Royel]

In this case you can make your slicer as single select, therefor default result will be for a single items which will ensure initial load faster and when it needs user can switch slicer values. 

[rgouette]

Royel.. not sure what's different about how I'm doing this, but it's not working(meaning , still reading all rows).

Here's what I just tried:

I have 3 visuals on my report.
Top is a slicer , set for, Vertical List, Single Select=ON
My D

SQL query is currently set with a WHERE clause, so that I can get the report to load.
e.g.  (WHERE glr.ID = 100317)

So, I now go to my query, & remove the WHERE clause:
I'm currently waiting for the report to finish applying the change(it's been 2+ minutes so far)

and this is what I get:
Message=Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

So, it's clearly more than just setting the slicer to single select..

😕

[Royel]

You are making mistake here,

SQL query is currently set with a WHERE clause, so that I can get the report to load.
e.g.  (WHERE glr.ID = 100317)

Your query should be paramitarized other wise you are not able to change value from slicer. 

Again i recommand you to follow from step 2

[rgouette]

Hi again, I cannot seem to get a result when I put in a REquestID in as a parameter..

In case this helps clear up  anything, here's the SQL query I'm working with:

SELECT DISTINCT
    glr.ID as 'RequestID',
    ar.REQUEST_ID as 'AssayReq ID',
    arp.ASSAY_RUN_ID,
    pp.PRIMER_ID,
    pp.PRIMER_NAME,
    pp.SEQUENCE_TXT, 
    glr.JRS_NO AS "GL_REQ_JRS-No",
    glr.STATUS_TYPE,
    gla.CREATE_DATE AS "Req Create Date",
    ar.JRS_NO AS "GL_Assay_Run",
    ar.ALLELE_ID,
    gla.ALLELE_SMBL,
    ar.TAB_LABEL AS "Assay ID",
    ar.CREATE_DATE,
    glsd.STRAIN_NAME,
    pwp.WORKING_PROTOCOL_ID AS WPID_PWP,
    arp.WORKING_PROTOCOL_ID AS WPID_ARP,
    pr.WORKING_PROTOCOL_ID AS WPID_PR,
    pr.REACTION_ID,
    prc.REACTION_ID AS REACTION_ID_PRC,
    ar.MGR_REVIEW_DATE,
    pr.REACTION_NAME,
    

    -- Distinct count of SEQUENCE_TXT per glr.ID
    (
        SELECT COUNT(DISTINCT pp_inner.SEQUENCE_TXT)
        FROM PRTL_PRIMER pp_inner
        JOIN prtl_protocol_primer ppp_inner ON pp_inner.PRIMER_ID = ppp_inner.PRIMER_ID
        JOIN PRTL_WORKING_PROTOCOL pwp_inner ON ppp_inner.master_protocol_id = pwp_inner.master_protocol_id
        JOIN GL_ASSAY_RUN_PROTOCOLS arp_inner ON pwp_inner.WORKING_PROTOCOL_ID = arp_inner.WORKING_PROTOCOL_ID
        JOIN GL_ASSAY_RUN ar_inner ON arp_inner.ASSAY_RUN_ID = ar_inner.ID
        WHERE ar_inner.REQUEST_ID = glr.ID
    ) AS [Primer Count],
    pr.TOTAL_VOLUME_AMT,
    ar.TSTTYPE as 'Assay Type',
    
    
    -- Max SAMPLE_NO per REQUEST_ID
    (
        SELECT TOP 1 MAX(gs.SAMPLE_NO)
        FROM [TGSDB].[dbo].[GL_SAMPLE] gs
        WHERE gs.REQUEST_ID = glr.ID
    ) AS Max_Sample_No,
    gar.GENOTYPE



    

FROM gl_request AS glr
LEFT OUTER JOIN GL_ASSAY_RUN AS ar ON ar.REQUEST_ID = glr.ID
LEFT OUTER JOIN GL_ASSAY_RESULT gar ON gar.ASSAY_RUN_ID = ar.ID
LEFT OUTER JOIN GL_ASSAY_RUN_PROTOCOLS AS arp ON arp.ASSAY_RUN_ID = ar.ID
LEFT OUTER JOIN PRTL_REACTION AS pr ON pr.WORKING_PROTOCOL_ID = arp.WORKING_PROTOCOL_ID
LEFT OUTER JOIN GL_ALLELE AS gla ON gla.ALLELE_ID = ar.ALLELE_ID
LEFT OUTER JOIN GL_STRAIN_DICT AS glsd ON glsd.JRS_NO = glr.JRS_NO
LEFT OUTER JOIN PRTL_WORKING_PROTOCOL AS pwp ON pwp.WORKING_PROTOCOL_ID = arp.WORKING_PROTOCOL_ID
LEFT OUTER JOIN prtl_protocol_primer AS ppp ON ppp.master_protocol_id = pwp.master_protocol_id
LEFT OUTER JOIN PRTL_REACTION_COMPONENT AS prc ON pr.REACTION_ID = prc.REACTION_ID
LEFT OUTER JOIN PRTL_COMPONENT AS pc ON pc.COMPONENT_ID = prc.COMPONENT_ID
LEFT OUTER JOIN PRTL_PRIMER AS pp ON pp.PRIMER_ID = ppp.PRIMER_ID
LEFT OUTER JOIN prtl_protocol_primer AS prpp ON prpp.master_protocol_id = pwp.master_protocol_id
Left Outer Join GL_PROTOCOL_genotypes_dict gpgd ON gpgd.PROTOCOL_ID = pwp.MASTER_PROTOCOL_ID

    AND (
        prpp.effective_date IS NULL OR
        CAST(prpp.effective_date AS DATE) <= CAST(GETDATE() AS DATE)
    )
    AND (
        prpp.termination_date IS NULL OR
        CAST(GETDATE() AS DATE) < CAST(prpp.termination_date AS DATE)
    )

[Royel]

Hi, lets make this easy. At first take my shared file and follow the guideline only for single line sql query. Once, you setup the system then replace the query with actual query. It will help you to do it faster.

Alternate solution: 

Using SQL:

1. Convert your query as stored procedure. 

2. Create a table and insert values through stored procedure

3. Delete rows and insert again based on your needs. 

4. Windows jobs will help you to make the process automatic. 

If you use Fabric, you can do the same things with help of Notebook and pipeline. 

How, it will help:

It will complete the data processing internally and store the results in a table so result will be lower number of rows than actual and this table will be very easy to load in Power BI. 

[rgouette]

Royel, I'm sorry to be dumb, but how can I run it when the datasource is: 

[Royel · Accepted solution]

FYI, 

Source = Sql.Database("DESKTOP-BV0J2NP\SQLEXPRESS", "ContosoRetailDW")
Here
Server: DESKTOP-BV0J2NP\SQLEXPRESS

Database: ContosoRetailDW
you need to replace this with your information, when you complete the steps you will get edit alert. Click there and configure with your email address or user name and password. 

[rgouette]

I am going to revisit that post, and see if I can get it right.

Thank you for hanging in there with me....

~Rich

[v-saisrao-msft]

Hi @rgouette,

Just checking in have you managed to implement the parameterized query solution and confirm that it works as expected?
If it is working, please provide your insights on the issue so it can help other community members.

Thank you.

[v-saisrao-msft]

Hi @rgouette,

Have you had a chance to review the solution we shared by @Royel @Nabha-Ahmed @kushanNa @Shahid12523? If the issue persists, feel free to reply so we can help further.

Thank you.

[Nabha-Ahmed · Accepted solution]

Hi @kushanNa 

The main issue is that Power BI queries the source to populate dropdowns or parameters. To avoid scanning millions of rows:

1. Use a small parameter/disconnected table for your dropdowns (Top 1000 IDs, recent IDs, etc.).

2. Create a filtered SQL view that limits rows before Power BI connects.

3. If possible, use Incremental Refresh to reduce queries on report load.

4. Avoid pointing slicers directly at the full table—always use a smaller supporting table.

 This approach ensures the report loads quickly without querying the entire dataset.

Find this helpful? Give a Kudo • Mark as Solution – help others too!

[rgouette]

Hi, I have a disconnected table that contains 1 column: "ID".

That is joiuned to my "main" table to "RequestID".
My splicer is bound to that table to the only column(ID)
When I drop that single column onto my report, it ONLY returns 1000 rows....
??
How do I tell PowerBi to show me millions of rows, without telling it...show me millions of rows?!

[kushanNa]

Hi @rgouette 

I think the best option for you is to go with DirectQuery mode. However, I understand that DirectQuery sends a query call to your data source for each visual. So, if you have a visual—like a chart—that needs to show all data on the first load without scrolling, it will pull all the data at once. Unfortunately, I don’t have access to a massive database right now to test your scenario. You could try adding a default parameter at the start, such as top100 , or any way to limit the data initially. Once the report loads, let users change the value from a dropdown to see if it works

[rgouette]

the issue setting a TOP value, is that when you remove that limit, the query then scans the entire dataset... 11 million rows, in this case..

[rgouette]

Royel, I decided to start fresh with  anew report so I'm not bringing doubt into this.
So, I'm connecting to SQL, I put in SErvername, I put in DB name.

I select Direct Query.

I paste my query.

If I paste the query without  a clause, it's trying to pull in millions of records....

I changed to TOP 1000 and it loaded right away.

Is there some other way of initially creating a report??

or is this simply what people do, and you remove the TOP restriction later?

Rich

[Royel]

Not like that, 

Lets do a test, at first run your query in the ssms and check how much time it takes to return the results, if its taking more time as expected you need to optimize your query. 

Now for our solution

On the step 2 you need to ensure you select direct query.  

Step 3: when you prepare your slicer dataset you can test it just by a single value if it works make the parameter value ALL. 

It should take very less time because (by default power query will show top 1000 rows) and your power bi file size should be very low like less than 1 MB. 

You can take my sample power bi file as an example and update based on your information. 

[Royel]

Hi @rgouette  I tried to build a solution to overcome this issue. 

What i used: Parameter and Direct Query 

Here is the process: 

1. Create Parameter: Power Query -> Manage Parameters 

2. Data: Create a blank query -> Advance Editor ->Pest this ->  [You need to update the server, DB and table]

let
    Source = Sql.Database("DESKTOP-BV0J2NP\SQLEXPRESS", "ContosoRetailDW"),
    FilteredQuery = if IDParameter = null or IDParameter = "" or IDParameter = "ALL"
        then "SELECT * FROM [dbo].[FactSales]"
        else "SELECT * FROM [dbo].[FactSales] WHERE StoreKey = " & IDParameter,
    CustomQuery = Value.NativeQuery(Source, FilteredQuery)
in
    CustomQuery

 3. Create another dataset for Slicer 

let
    Source = Sql.Database("DESKTOP-BV0J2NP\SQLEXPRESS", "ContosoRetailDW"),
    StoreList = Value.NativeQuery(Source, 
        "SELECT DISTINCT 'Store ' + CAST(StoreKey AS VARCHAR) AS DisplayName, StoreKey 
         FROM [dbo].[FactSales] 
         WHERE StoreKey IS NOT NULL 
         ORDER BY StoreKey"
    )
in
    StoreList

Note: you can test by entering any value for IDParameter and it should work for ALL

4. Close and Apply 

5. Create a relationship 

6. Create your visual and put a column name (Ex: Store Name) in your slicer 

7. Publish the report and click semantic model -> settings configure your connection and ensure parameter has value ALL 

8. Here is the results 

So, in simple what is happening in here, Instead of pulling all the millions of records into Power BI and then filtering, it only fetches the filtered records directly from the database. This makes the system faster and avoids endless loading.

Here is the file: https://drive.google.com/file/d/1JwrnjpRuDkCGkPKo1tQuanxaUB9RRH-P/view?usp=drive_link 

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!