Allowing Dynamic user inputs on a Power BI dashboard

[DavidCather]

Hi, 

I'm working on a dashboard to model staffing. e.g. How many staff do we have, how many do out projects need and so on.

I want to allow the user to consider scenarios e.g. what if the retirement age changed.  What ever value the user sets the retirement age as, several row-by-row calculations need to use the value in their calculations.  I thought I could do this with a parameter, but I've realised the parameter creates a measure to store the selected value and measures can't be used in row-by-row calculated column calculations.

Has anyone got any ideas of how to achieve this?

[DavidCather]

Hey, hopefully this explains the problem in more detail. I have a table of all the staff in the company.

Supply Table

Name	Date of birth	Firmwide Retirement	Retirement Date	Role
John Smith	01/05/1960	65	30/04/2025	Project Manager
Jane Bloggs	23/04/1986	65	22/04/2051	Engineer

And a table of all Projects that the company needs to staff.

Demand Table

Project	Start Date	End Date	Project Managers Required	Engineers Required
Project ABC	01/04/2021	31/12/2024	3	24
Project ZYX	01/11/2020	30/04/2027	7	32

Extrapolating the supply forward, I will have a view of the total available staff theoretically available in each year.  I can then compare this to the Demand table and show whether there is projected to be an over or undersupply of staff to fulfil project roles.  This allow a gap analysis like this to be plotted

Now the challenging part, I want to allow the user to change variables in the dashboard and see the effect this will have on supply, so I would like to have an input slider to change the retirement age, this number will need to be used to recalculate the retirement age of the staff member.  And that in turn will affect the calculation of whether they are available or not on a certain date in the future.  I'm open to suggestions as to how this can be achieved, I've been looking at the problem long enough now to see that it needs to be a workaround.

Thanks.

[Anonymous · Accepted solution]

Hi @DavidCather ,

I think you can set parameters.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-what-if 

Users can change the parameters and get dynamic value.

You may also check about the document about Pin live page feature if you want it worked in dashboard.

https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboard-pin-live-tile-from-report 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Hi @DavidCather ,

Can you show more details? You want create calculated column by selected value in the measure?

Best Regards,

Jay

[DavidCather]

Hey, hopefully this explains the challenge in more detail.  So I have a table of all the staff in the company.

Supply Table

Name	Date of birth	Firmwide Retirement	Retirement Date	Role
John Smith	01/05/1960	65	30/04/2025	Project Manager
Jane Bloggs	23/04/1986	65	22/04/2051	Engineer

And a table of all Projects that the company needs to staff.

Demand Table

Project	Start Date	End Date	Project Managers Required	Engineers Required
Project ABC	01/04/2021	31/12/2024	3	24
Project ZYX	01/11/2020	30/04/2027	7	32

Extrapolating the supply forward, I will have a view of the total available staff theoretically available in each year.  I can then compare this to the Demand table and show whether there is projected to be an over or undersupply of staff to fulfil project roles.  This allow a gap analysis like this to be plotted

Now the challenging part, I want to allow the user to change variables in the dashboard and see the effect this will have on supply, so I would like to have an input slider to change the retirement age, this number will need to be used to recalculate the retirement age of the staff member.  And that in turn will affect the calculation of whether they are available or not on a certain date in the future.  I'm open to suggestions as to how this can be achieved, I've been looking at the problem long enough now to see that it needs to be a workaround.

[Greg_Deckler]

@DavidCather You can generally get the same effect but would need to know more about your original issue. 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[mwegener]

Hi @DavidCather ,

check this Visual.

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA200000006?src=office&tab=Overview