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

[Anonymous]

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)

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

Many thanks, any help would be appreciated.

Best, Dytong

[saglamtimur]

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.

[Anonymous · Accepted solution]

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.