Forum Discussion
How to define a simple parameters table when using a shared Power BI dataset ?
Hi All,
Please, consider this abstracted business scenario.
Background
We are a team of report authors who are building reports on a centralized shared Power BI dataset.
Tables in the model
- Sales
- Users
Sample data
Users table
Sales table
Relationship
1 to many relationship between Users and Sales table
Business requirement
Show me sales persons from Users table who have accummulated average sales which are above a certain configurable threshold. The threshold value should be driven by a slicer.
| userid | First name | Last name | Average Sales |
| jane | Jane | Doe | ??? |
| john | John | Doe | ??? |
Approach taken
Step 1 - Add a new parameters table 'SalesThresholdParameter'
SalesThresholdParameter = GENERATESERIES(0,1000,1)
Do this in the Shared model
Step 2 - Created a new measure AverageSalesUsingThreshold
AverageSalesUsingThreshold =
VAR threshold=MIN(SalesThresholdParameter[Value])
VAR avgSales=AVERAGE(Sales[sales])
VAR avgSalesZeo=IF (ISBLANK(avgSales),0,avgSales)
VAR diff = avgSalesZeo - threshold
RETURN IF(diff >= 0, diff, BLANK())
The measure returns a BLANK() when average sales is below the selected threshold
Step 3 - Create a Table visual and Slicer
- Add the column AverageSalesUsingThreshold to the table visual
- Add a slicer which slices the parameters table that was created using GENERATESERIES
Rows where the measure AverageSalesUsingThreshold returned BLANK() are dropped automtically. This helps us to achieve our objective.
Is there a better approach - where I do not need to edit the Shared model?
In the above approach, we created a new table by using GENERATESERIES. This can only be done through one of the following ways:
- Add the parameters table to the shared model
- Convert to a local model and define the new parameters table in this model
- We do not want to go back to the Shared dataset and keep adding such tables - too much testing and administrative effort
- We do not want to create new local models, because deployment becomes tougher. You now have another Dataset which has to deployed and permissioned.
Links to sample Excel and PBIX
Any suggestions?
thanks,
Sau
2 Replies
The parameter table has to live somewhere and I don't think there are any reasonable options other than in the shared model or in a local model.
My general heuristic for this is that if it's a parameter table that will be used in more than one report, stick it in the shared model for the sake of unification. If it's a one-off thing that isn't likely to ever be used in any other report, then maybe do it locally.
Note that it's possible to create parameters that are more general than case-specific to reduce clutter. For example, you could create ThresholdParameter rather than SalesThresholdParameter and use it for things other than sales in other reports.
Anonymous I don't see any other approach except what you have listed above. Somewhere you have to define the what-if parameter and you already listed the options. If I were you I will add it to the shared dataset, and go thru the process of rolling out in the production rather than using 2nd option which will lead to many more models. Just my thoughts on this.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
