cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Filters don't populate with values when using SQL data. Bower BI Report builder

Good Day,

 

I am using Power BI report builder, as I want to create reports per category (voting wards in the City of Johannesburg in this case).

 

I thus need to use filters.

 

When I load the data from a power BI Dataset connection, the filter fields at the top of the report propogate with the values in the chosen filter (ward number). Using power BI data is very slow though (it must query the system online) so I installed Microsoft SQL Server Express to host the data. It solved the speed issue (it now works really quickly). 

 

However: now when I connect to the data from my local SQL server, and add a filter, the filter does not contain the values in that field (ward number). I would need to manually add the values. I added the data to the server by importing an excel file.

 

How do I set up the data so that the filter values show? Is there something else I have to do to prepare the data such as index it, ir transform it in some way?

 

This first screenshot is what I want (this is using a power bi data connection, but which is very slow)

 

dyltong_0-1592553385492.png

 

But this is what I get when using SQL server, with data imported from an excel file:

 

dyltong_0-1592554003306.png

 

Many thanks, any help would be appreciated.

 

Best, Dytong

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
New Member

Re: Filters don't populate with values when using SQL data. Bower BI Report builder

Ok, I see you need analysis server which is not available with SQL express. Will try a trial version of SQL or azure analysis server.

View solution in original post

2 REPLIES 2
Highlighted
Resolver II
Resolver II

Re: Filters don't populate with values when using SQL data. Bower BI Report builder

It's normal behaviour of SQL query designer. For example, SSRS designer in Visual Studio, which is mature tool then any others, also doesn't show up values for SQL queries. You have to manually provide values.
But this is not true for MDX/DAX queries. You can view all values and select.

Highlighted
New Member

Re: Filters don't populate with values when using SQL data. Bower BI Report builder

Ok, I see you need analysis server which is not available with SQL express. Will try a trial version of SQL or azure analysis server.

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.