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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
saglamtimur
Resolver II
Resolver II

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
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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