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

What-if Parameter for Only Selected Values

Hey,

 

I'm trying to create visualizations for managing project resources and what-if scenarios. I have a table where I have the demand, a table where I have the project names, and a table where I have the resource names and their roles. 

 

I have succesfully created a parameter which allows me to adjust the demand for all projects/resources, but I would like to be able to choose only specific projects or resources and adjust their demand. 

 

So basically I have a matrix that would look like this:

Project Name             Demand

Project 1                      100

Project 2                      50

Project 3                      150

Total                            300

 

I would like to be able to choose only project 1 and adjust the demand by 10 % so the table would end up looking like this:

Project Name             Demand

Project 1                      110

Project 2                      50

Project 3                      150

Total                            310

 

But instead when I adjust the demand it always adjusts all the values by 10 % so that the total ends up to 330. 

 

My current Adjusted Demand uses this query:

Adjusted Demand = 

VAR

     AdjustedDemand = INTERSECT( VALUES( ProjectData[Project Name]), ALL(ProjectData[Project Name]))

RETURN

CALCULATE(

     SUMX(ResourceDemandData, (ResourceDemandData[Demand] * (1 + [What-if Demand Value]))), 

     AdjustedDemand)

 

Is there something wrong with the query and how should it be changed to only apply to the selected projects?

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

1.

Create a new table which has no relationship with other tables

slicer_project = SELECTCOLUMNS(VALUES(project[project]),"slicer_project",[project])

Add "slicer_project" in the slicer.

 

2.

Create what-if parameter

this is a measure from "parameter" table

Parameter Value = SELECTEDVALUE('Parameter'[Parameter])

 

3.

create measures in "project" table

Measure =
IF (
    SELECTEDVALUE ( slicer_project[slicer_project] ) = MAX ( project[project] ),
    MAX ( demand[demand] ) * ( 1 + [Parameter Value] ),
    MAX ( demand[demand] )
)

Measure 2 = IF(HASONEVALUE(project[project]),[Measure],SUMX(ALL(project),[Measure]))

7.png

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

1.

Create a new table which has no relationship with other tables

slicer_project = SELECTCOLUMNS(VALUES(project[project]),"slicer_project",[project])

Add "slicer_project" in the slicer.

 

2.

Create what-if parameter

this is a measure from "parameter" table

Parameter Value = SELECTEDVALUE('Parameter'[Parameter])

 

3.

create measures in "project" table

Measure =
IF (
    SELECTEDVALUE ( slicer_project[slicer_project] ) = MAX ( project[project] ),
    MAX ( demand[demand] ) * ( 1 + [Parameter Value] ),
    MAX ( demand[demand] )
)

Measure 2 = IF(HASONEVALUE(project[project]),[Measure],SUMX(ALL(project),[Measure]))

7.png

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thank you very much for your answer, it worked 🙂 

To take things to the next level, can you help if it would it be possible to select multiple projects and change the demand for all of them? 

 

For example if I wanted to choose both projects 1 & 2 and change the demand by 10 % but leave project 3 the way it is. 

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