cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlejandroG
Resolver II
Resolver II

Tablix filter forced to text when Data Source is a Power BI dataset - Report Builder

If this is not the correct forum please, change the thread.

 

When I use a Power BI dataset as the datasource for the report, the filter of the tablix is forced to text. This does not happen when the datasource is a sql server.

pbix datasource (of course, that field is of class Date in the model)pbix datasource (of course, that field is of class Date in the model)sql server datasourcesql server datasource

Anyone knows how to solve this?

4 REPLIES 4
Dribblej
Frequent Visitor

I have this problem still today in feb 2021. Has anyone figured this out?

d_gosbell
Super User II
Super User II

This can happen if either the column in your tabular model is set to be a string data type. Or if you are using an MDX query where all non-measures are implicitly converted to strings.

 

So if either of these are the cause you could either change the data type in your model or change to using a DAX query.

 

Or as a final resort you could maybe create a calculated column in your dataset and use DateTime.Parse() to get a strongly typed DateTime column.

I just made a test .pbix to check if it was the PBI data model I was using.

I uploaded to the service this .pbix file:

1 - pbi model1 - pbi model

Then I brought it to the Report Builder:

2 - creating the dataset (date field now has time added??)2 - creating the dataset (date field now has time added??)

3 - it renders ok3 - it renders ok

However, the date field is locked as text in the dataset filter, this does not happen if the source is an sql server database:

4 - date field treated as text in the filter menu of the dataset4 - date field treated as text in the filter menu of the dataset

I just started using Report Builder 5 minutes ago (not literally :P) so maybe I´m missing something, but this looks like a bug to me.

 

Hello @d_gosbell, thanks for your answer. 

Unfortunately, the query is DAX and the column in the Power BI model is definitely of datetime type.

However, I tried using DateTime.Parse() as you suggested. I made a calculated field like this:

temp_rb.JPG

The new field, ParsedDate, could indeed be used to filter by date as you can see in the image below, but I don´t think it was a "strongly typed DateTime column" because it was not forced to DateTime, you could choose any type:

temp_rb2.JPG

Also, you can choose any type, but when you try to render the report the following error pops up:

Failed to evaluate the FilterExpression of the DataSet ‘DataSet1’. It references a dataset field which has an error: FieldValueException.

And when you open the filter again it has defaulted to text (the selection you did before did not stick, and changes by itself to text).

 

I believe this is a bug. It seems like column types do not get carry over when you choose a PBI dataset as a source in Report Builder (direct sql server connection works fine).

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates