Forum Discussion
Paginated Report : numeric parameter - issues with decimals
- Anonymous1 year ago
Hi,Pops1997 ,I am glad to help you.
I think your guess is probably right.
Also I have a possible guess about the problem you are experiencing.
You describe that when an expression is used, selecting one value is queried, but when multiple values are selected only the first value is returned
Like this.In fact I noticed that this may be due to the way your expression is written
=replace(Parameters!KPIMTCredit.Value(0), ‘.’ , ‘,’)
Parameters!KPIMTCredit.Value(0) will only return the first value that
You have selected .Value(0), which represents the first value in the list, and .Value(1) which represents the second value in the list
Your expression only modifies the first data in the filter condition so even if you replace ‘.’ is replaced with ‘,’ the only result that will actually be successfully filtered in the paginated reports is the first one (because you have limited the expression to index = 0).
So there will be problems when the user selects multiple values. To handle multiple values
You can try removing the subscript ‘(0)’ from the expression=replace(Parameters!SECTION.Value,',','~')//When selecting more than one value you need to use the Join function, this way of writing will report an error
After I wrote it correctly as follows:=replace(Join(Parameters!SECTION.Value, " "), ",", "~")The replace function takes a string as input.
In order to work with multiple values, you need to convert the array to a string first. You can use the Join function to concatenate all the values in the array into a single string.
But this way is not always possible in paginated reports, because paginated reports automatically add ‘,’ between multiple parametersI think creating a new calculated column for the table in Power BI Desktop and using the data from the new calculated column as a slicer field is a much more convenient and simpler solution, and it doesn't require you to modify your paginated reports too much (modifying paginated reports can easily be problematic).
You can use the fotmat function in Power BI Desktop to change the decimal point data to comma text, and use this text column for filtering (placing it as a slicer option in the paginated reports visual) instead of using the real data with the original decimal point.
URL:
FORMAT function (DAX) - DAX | Microsoft LearnModify the filtered data in the paginated reports to text type to ensure that the data types match.
It ensures that the field types on both sides are text types and that there are commas instead of decimal points in the text.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,Pops1997 ,I am glad to help you.
I think your guess is probably right.
Also I have a possible guess about the problem you are experiencing.
You describe that when an expression is used, selecting one value is queried, but when multiple values are selected only the first value is returned
Like this.
In fact I noticed that this may be due to the way your expression is written
=replace(Parameters!KPIMTCredit.Value(0), ‘.’ , ‘,’)
Parameters!KPIMTCredit.Value(0) will only return the first value that
You have selected .Value(0), which represents the first value in the list, and .Value(1) which represents the second value in the list
Your expression only modifies the first data in the filter condition so even if you replace ‘.’ is replaced with ‘,’ the only result that will actually be successfully filtered in the paginated reports is the first one (because you have limited the expression to index = 0).
So there will be problems when the user selects multiple values. To handle multiple values
You can try removing the subscript ‘(0)’ from the expression
=replace(Parameters!SECTION.Value,',','~')
//When selecting more than one value you need to use the Join function, this way of writing will report an error
After I wrote it correctly as follows:
=replace(Join(Parameters!SECTION.Value, " "), ",", "~")
The replace function takes a string as input.
In order to work with multiple values, you need to convert the array to a string first. You can use the Join function to concatenate all the values in the array into a single string.
But this way is not always possible in paginated reports, because paginated reports automatically add ‘,’ between multiple parameters
I think creating a new calculated column for the table in Power BI Desktop and using the data from the new calculated column as a slicer field is a much more convenient and simpler solution, and it doesn't require you to modify your paginated reports too much (modifying paginated reports can easily be problematic).
You can use the fotmat function in Power BI Desktop to change the decimal point data to comma text, and use this text column for filtering (placing it as a slicer option in the paginated reports visual) instead of using the real data with the original decimal point.
URL:
FORMAT function (DAX) - DAX | Microsoft Learn
Modify the filtered data in the paginated reports to text type to ensure that the data types match.
It ensures that the field types on both sides are text types and that there are commas instead of decimal points in the text.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous !
Big Thank you for your help !!
So I tested different versions of your last suggestion, and I finally succeeded ! Here's the process :
- I duplicated my column in en-US format (not what I expected I'll be needing), with points as separators. Text.
= Table.AddColumn(Source, "MTCredit_US", each Number.ToText([MTCredit], "0.00", "en-US"),type text)
Then, I added the slicer to my report, while also keeping the old one, because I want my final users to select values by numeric order and non alphabetical.
"Credit" is my original parameter with numeric value, that's what the final user will use to select values.
"Credit US" becomes my new parameter (text) in the paginated, I'm going to hide it in the report.
And it works wonders !
Heavy process but I'm so relieved !