Why doesn’t SELECTEDVALUE work with Field parameters?
Building a report in Power BI, we encountered difficulties using the SELECTEDVALUE in combination with a field parameter.
In this post, we will show how we overcome this issue.
The problem:
The main goal of the business was to sell all products by a specific date (01/07/2025). To achieve this, the business required a dashboard displaying the total number of products in stock and the daily sales target needed to reach zero inventory by 01/07/2025. However, when we attempted to create a field parameter to switch between calendar and business days—adjusting the daily target and the number of days—it resulted in the following error:
Below you will find an illustrative example of the desired dashboard.
The details to create it will be explained in steps:
1 – Create a card for total number of products (measures).
Use the DISTINCTCOUNT function to count total number of products in stock.
2 – Business days and All days until 01/07/2025 (measures).
All day’s calculation: count total number of days from today until 01/07/2025.
Business days calculation: Count only business days from today until 01/07/2025.
3 – Create the field parameter.
A Field parameter was created to allow user to choose either “Business days” or “All days”.
We went to modelling then New parameter, and fields.
After that, we select the two measures Business days and All days.
4 – Calculate Target per day (measures).
This step shows the calculation on how many products need to be sold to achieve 0 products in the inventory by 01/07/2005. Here is where we found an issue with the SELECTEDVALUE function.
Initial Approach: Initially, we use a simple formula to calculate Target Product per day.
Using SELECTEDVALUE: Next, we tried to calculate using the SELECTEDVALUE directly on the field parameter.
However, this result in an error (below). It seems like the SELECTEDVALUE cannot be directly used to retrieve the selected item from a column used in the Field Parameter.
Solution to the SELECTEDVALUE Issue: The calculation below was created to solve the issue (measure).
The idea of this calculation is to summarize the parameter table in a single table and then create a conditional statement to handle the parameter selection.
5 – Final Result
To create the Target Products per day, the Day Divide Change measure was incorporated to the first calculation (measure).
With this approach, the calculation of the daily sales target (“target per day”) needed to achieve 0 products in the inventory by 01/07/2025 was successfully created.
For more information you can access this link: slqBI
I hope this information can be helpful. See you next time.