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

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

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.

 

image001.png

 

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.

5 REPLIES 5
Vickar
Advocate I
Advocate I

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
Community Support
Community Support

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.

 

39.png

 

 

 

 

 

 

 

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.

 

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.

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

Anonymous
Not applicable

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

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.

Top Solution Authors
Top Kudoed Authors