The property ‘ValidValues’ of report parameter ‘TransactionsShift’ doesn’t have the expected type

[Anonymous]

Hi Community,

Does anyone know how to change a Data Type on a column in PowerBI Report Builder? I have come to a dead end. I am unable to set a parameter as a ‘Date/Time’ Data Type.

I receive the following error: The property ‘ValidValues’ of report parameter ‘TransactionsShift’ doesn’t have the expected type.

I have managed to get my report working, but only when connected to a Microsoft SQL Server, or Azure SQL Database, but not when connected to a PowerBI Dataset.

[Vickar]

I overcame the issue by making the below changes

firstly, to avoid having other runtime errors, if the dataset for the report parameter ‘TransactionsShift’ was automatically genereted, then unhide this dataset and go to dataset properties, replace the dax query with 

EVALUATE SUMMARIZECOLUMNS('TableName'[TransactionsShift])

then in the fields tab of the dataset properties window, delete other fields and only keep the TransactionsShift field and hit save.

next, go to Report Parameter properties of TransactionsShift, in the available values tab, select "Get values from a query", select the dataset you just modified above, and select "TransactionsShift" for Value field and Label field and save.

secondly,

navigate to the main dataset that you are adding the above parameter to filter dates, under Dataset properties, go to "Parameters" tab, click the "fx" button on "TransactionsShift" parameter and add the below expression and save.

= FormatDateTime(Parameters!TransactionsShift.Value,DateFormat.ShortDate)

Now when you run the report, you should not see the subject error.

hope that helps. 

[v-xicai]

Hi @Anonymous ,

When you view a report that has parameters, the report viewer toolbar displays each parameter so you can interactively specify values. The following illustration shows the parameter area for a report with parameters @ReportMonth, @ReportYear, @EmployeeID, @ShowAll, @ExpandTableRows, @CategoryQuota, and @SalesDate.

For the mark 2 , @SalesDate parameter The parameter @SalesDate is data type DateTime. The prompt Select the Date appears next to the text box. To modify the date, type a new date in the text box or use the calendar control.

You can refer to the link: Report parameters in Power BI Report Builder.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mald0b]

I'm having the same issue.  Has this been resolved?  I can change the parameter type to text and it works but that is not user friendly.

[Vickar]

please refer to the detailed solution that I've posted.

[Anonymous]

Hi Amy,

Thanks for your post, but it does not resolve the issue I am posting about. When I connect to a SQL DB or Azure SQL DB, PBIRB recognises my date column as a date data type, but when I connect to a PowerBI Data Source, PBIRB seems to recognise my date column as a text data type, so I get an error when I set the paramater as a DateTime.

Here is the DAX query I am using to load the Dataset. 

If I can change the 'Transactions'[Shift] column to a date time Data type all should be good.

DEFINE
VAR TransactionsShift1 =
IF (
PATHLENGTH ( @TransactionsShift ) = 1,
IF ( @TransactionsShift <> "", @TransactionsShift, BLANK () ),
IF (
PATHITEM ( @TransactionsShift, 2 ) <> "",
PATHITEM ( @TransactionsShift, 2 ),
BLANK ()
)
)
VAR TransactionsShift1ALL =
PATHLENGTH ( @TransactionsShift ) > 1
&& PATHITEM ( @TransactionsShift, 1, 1 ) < 1
EVALUATE
SUMMARIZECOLUMNS (
'Transactions'[Shift],
'Transactions'[Shift3],
'Transactions'[EMIT_SUM_REF2],
'Transactions'[Balance],
'Transactions'[Purchase_Amount2],
'Transactions'[Return Value],
'Transactions'[O-Balance],
'Transactions'[Banked Amount],
FILTER (
VALUES ( 'Transactions'[Shift] ),
(
(
TransactionsShift1ALL
|| 'Transactions'[Shift]
= DATEVALUE ( TransactionsShift1 ) + TIMEVALUE ( TransactionsShift1 )
)
)
)
)