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

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

andriikubrak — Wed, 04 Oct 2017 13:45:46 GMT

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?

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

Eric_Zhang — Fri, 06 Oct 2017 09:40:54 GMT

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

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

andriikubrak — Fri, 06 Oct 2017 13:01:43 GMT

@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?