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

[Anonymous]

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)sql server datasource

Anyone knows how to solve this?

[AlronVanDiemen]

Hi 

Not sure if you were able to come to a solution or not. But for future reference to whoever stumbles across this.

I found that while adding a dataset, BEFORE you save the query and it loads into The Power BI report builder, you can go to the filters tab and then you can select date/time data type:

strangely though when you go back into it, it will reflect as text.

Before you do any of the above you first need to define your info that you are going to use as a parameter as a dataset:

then , create the parameter and link it to your data set:

Hope it helps. I am able to filter my report on the dates and use it as a filter between the selected dates.

[Dribblej]

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

[d_gosbell]

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.

[Anonymous]

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 model

Then I brought it to the Report Builder:

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

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

[Anonymous]

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:

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:

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