Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
zrobins
Frequent Visitor

Data Missing in Table Visual and Filter

Currently working within Power BI Desktop, direct querying roughly 2.5 million rows from data source. Other tables within report, but all are independent of one another; no relationships exist between tables. No filters currently on across report, nor are there filters applied to this in the Power Query editor.

 

The issue I am having is that within the report view, I have data that I know exists within the data source that does not appear within the table visual,slicers, or filters unless I specifically call them out in the advanced filtering.

 

Example, in a list of email addresses, I know that 'johnsmith123@email.com' exists. However, in the table visual of email addresses in PBI, 'johnsmith123@email.com' is not present. Also, in the basic filtering pane, 'johnsmith123@email.com' does not exist there either. It isn't until I use advanced filtering to look for 'johnsmith123@email.com' does the email appear in the table visual. Without applying the advanced filter, this data is not present in the visual. 

 

I know that the table visuals have a row limit of a certain number of rows, but am curious as to why specific values are not present unless I filter on them specifically? I do not believe it to be a direct query issue as the data exists, but not certain if it is a storage issue or visual issue or what.

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi, @zrobins 

It may be a known limitation of using directquery .

Limit of 1 million rows returned on any query: There's a fixed limit of 1 million rows placed on the number of rows that can be returned in any single query to the underlying source. This limit generally has no practical implications, and visuals themselves aren't going to display that many points. However, the limit can occur in cases where Power BI isn't fully optimizing the queries sent, and there's some intermediate result being requested that exceeds the limit. It can also occur while building a visual, on the path to a more reasonable final state. For example, including Customer and TotalSalesQuantity would hit this limit if there were more than 1 million customers, until some filter were applied.

The error that would be returned would be: "The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows."



Users of this table can only see 1,000,000 rows at any time. This causes them need to filter down to a fine-grained level in order to find exactly what they want.

 

It is suggested  that you create an additional granularity slicer applied on the table.

For example, you can extract the first character of the email address  to create a new calculated column as a new slicer.

 

 

First character = LEFT(Table[EMAIL],1)

 

 

 

In this way,  you need to filter out all email addresses starting with "j"  through this  new slicer first, and then you can filter out the specific "johnsmith123@email.com"  through your original slicer.

 

95.png

 

 

Best Regards,
Community Support Team _ Eason

 



 

View solution in original post

6 REPLIES 6
v-easonf-msft
Community Support
Community Support

Hi, @zrobins 

It may be a known limitation of using directquery .

Limit of 1 million rows returned on any query: There's a fixed limit of 1 million rows placed on the number of rows that can be returned in any single query to the underlying source. This limit generally has no practical implications, and visuals themselves aren't going to display that many points. However, the limit can occur in cases where Power BI isn't fully optimizing the queries sent, and there's some intermediate result being requested that exceeds the limit. It can also occur while building a visual, on the path to a more reasonable final state. For example, including Customer and TotalSalesQuantity would hit this limit if there were more than 1 million customers, until some filter were applied.

The error that would be returned would be: "The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows."



Users of this table can only see 1,000,000 rows at any time. This causes them need to filter down to a fine-grained level in order to find exactly what they want.

 

It is suggested  that you create an additional granularity slicer applied on the table.

For example, you can extract the first character of the email address  to create a new calculated column as a new slicer.

 

 

First character = LEFT(Table[EMAIL],1)

 

 

 

In this way,  you need to filter out all email addresses starting with "j"  through this  new slicer first, and then you can filter out the specific "johnsmith123@email.com"  through your original slicer.

 

95.png

 

 

Best Regards,
Community Support Team _ Eason

 



 

v-easonf-msft
Community Support
Community Support

Hi, @zrobins 

Have you checked if there are other filters applied to the table?
If you use fields from multiple tables to build a table visual,please check whether there is a null value in some column corresponding to this email and  you have disabled the feature "show items  with no data" .

 

If the problem persists, it is recommended to create a support ticket to let MS engineers look into the issue on your side. https://powerbi.microsoft.com/en-us/support/

 

Best Regards,
Community Support Team _ Eason

This was my problem! Thank you for the hint. Best regards.

Thank you for your response. I am pulling from data from a single table that is direct queried from a db. The table visual only has email values listed in it, and I am not seeing the value that I know exists until I filter. When enabling the "show items with no data" option on the email value, I get an error that there is a limit of '1000000' rows. So, I am not able to see if enabling items with no data fixes the issue as the table visual has a limit to number of rows allowable. 

 

I'll enter a support ticket, I appreciate your time and effort on this.

v-easonf-msft
Community Support
Community Support

Hi, @zrobins 

You can  take a try to refresh the data preview  using "Refrsh preview" in the Query Editor.

51.png

Best Regards,
Community Support Team _ Eason

Thank you for your response. I have refreshed the preview within the power query editor, and I have done an overall refresh of the report as well. Unfortunately, that did not fix the issue. The issue is that the value that I know exists is not showing in the filter pane or in the visual that I have unless I do an advanced filter on the visual to show it. Please see below for what I mean:

zrobins_0-1606922797009.png

 

Once I apply the filter to the visual, I am able to see the value.

 

In the power query editor, I can run the text filter on the email column to find the value exists. Please see below for what I mean:

zrobins_1-1606922990704.png

 

 

I can filter the data to see that the data exists, but without filtering the data on the specific value, it does not appear in either the filter pane nor in the visual itself. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.