cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andriikubrak Frequent Visitor
Frequent Visitor

Filters API does not work using SQL Server with DirectQuery and huge database

I am using SQL Server as a data source with DirectQuery. There is just one table with 6 fields that contains more than 100 million rows. My report contains one visual "Card" that should display count of values from particular field. This report is embedded on a web page. As I don't want to get count of all values I use Power BI API to filter by some name. My filter looks like this:

Snippet

var filter = {
    $schema: "http://powerbi.com/product/schema#advanced",
    target: {
        table: "MyTable",
        column: "MyColumn"
    },
    logicalOperator: "And",
    conditions: [
      {
          operator: "Contains",
          value: "FilteredValue"
      }
    ]
};

 The problem is that it doesn't get any result. I can see just loading spinner on my visual. I've tried the same filter for small database with 10 rows and it works as expected.

Any ideas?

2 REPLIES 2
Moderator Eric_Zhang
Moderator

Re: Filters API does not work using SQL Server with DirectQuery and huge database


@andriikubrak wrote:

I am using SQL Server as a data source with DirectQuery. There is just one table with 6 fields that contains more than 100 million rows. My report contains one visual "Card" that should display count of values from particular field. This report is embedded on a web page. As I don't want to get count of all values I use Power BI API to filter by some name. My filter looks like this:

Snippet

var filter = {
    $schema: "http://powerbi.com/product/schema#advanced",
    target: {
        table: "MyTable",
        column: "MyColumn"
    },
    logicalOperator: "And",
    conditions: [
      {
          operator: "Contains",
          value: "FilteredValue"
      }
    ]
};

 The problem is that it doesn't get any result. I can see just loading spinner on my visual. I've tried the same filter for small database with 10 rows and it works as expected.

Any ideas?


@andriikubrak

Would the loading spinner finally finish and visual get loaded with data? I think is a performance issue which is limited by your Azure database service tier.

andriikubrak Frequent Visitor
Frequent Visitor

Re: Filters API does not work using SQL Server with DirectQuery and huge database

@Eric_Zhang

After waiting for half an hour loading spinner stopped and I got a message "Couldn't load the data for this visual" with Http Status Code: 500. What do you mean by Azure SQL Database? How is this related to my case if I use MS SQL Server as a database server? Is there possibilty that PowerBI doesn't handle such big data?

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 250 members 2,998 guests
Please welcome our newest community members: