cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Ten capacity projects with slicer selectable years to add capacity to baseline capacity.

I have a table of capacity by year for ten years:

 Year Capacity 2020 100 2021 120 2022 150

and a table with several projects and the resulting capacity that they add:

 Project # Capacity Addition 1 10 2 20 3 30

I also made an individual table for every project that contains all the possible year choices and None:

PROJECT1

 Selected Year None 2020 2021....

I need a visualization that lets you select a year via a slicer for each project (or None). The capacity addition for that project then gets added to the capacity for that year and all years after that. For example, if 2021 is select for Project 1 then the capacity table would look something like this:

 Year Capacity New Capacity 2020 100 100 2021 120 130 2022 150 160

I was able to create a measure that becomes the selected year fairly easily, but the problem I'm having is that you cannot use measures in Calcualted Columns because the aren't dynamic. So this doesn't work.:

``````IF(IFERROR(CONVERT(SELECTEDVALUE(Project[Selected Year],10000),INTEGER),10000)<= 'Sum Table'[Year],'Sum Table'[Capacity]+LOOKUPVALUE('Project Data'[Total Capacity Added],'Project Data'[Roadmap Projects],"1"),0)+

To throw another wrench in the mix, there are multiple baseline capacity options that I need to be able to select from. They are all in the same table.

How can I get this to work?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Community Support

Hi @CazCzw ,

Please check whether the below measure is what you want.

``````New Capacity =
VAR _selproj =
SELECTEDVALUE ( 'Projects'[Project #] )
CALCULATE (
FILTER ( 'Projects', 'Projects'[Project #] = _selproj )
)
RETURN
MAX ( 'Capacity'[Capacity] ) + _additionalcap`````` If the above measure is not suitable for your scenario, please provide more sample data in your model and your expected result using data examples. Thank you.

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

2 REPLIES 2
Highlighted Community Support

Hi @CazCzw ,

Please check whether the below measure is what you want.

``````New Capacity =
VAR _selproj =
SELECTEDVALUE ( 'Projects'[Project #] )
CALCULATE (
FILTER ( 'Projects', 'Projects'[Project #] = _selproj )
)
RETURN
MAX ( 'Capacity'[Capacity] ) + _additionalcap`````` If the above measure is not suitable for your scenario, please provide more sample data in your model and your expected result using data examples. Thank you.

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

Highlighted
Regular Visitor

I was able to use the solution I accepted to create the code that I needed. Ulitimately it looked like this:

``````RETURN
IF(MAX('Sum Table'[Year])>=yr1,cap1,0)+
IF(MAX('Sum Table'[Year])>=yr2,cap2,0)+
IF(MAX('Sum Table'[Year])>=yr3,cap3,0)+
IF(MAX('Sum Table'[Year])>=yr4,cap4,0)+
IF(MAX('Sum Table'[Year])>=yr5,cap5,0)+
IF(MAX('Sum Table'[Year])>=yr6,cap6,0)+
IF(MAX('Sum Table'[Year])>=yr7,cap7,0)+
IF(MAX('Sum Table'[Year])>=yr8,cap8,0)+
IF(MAX('Sum Table'[Year])>=yr9,cap9,0)+
SUM('Sum Table'[Capacity])``````

Where each yr is the slicer selection for the year as an int (if it fails to convert then goes to 10000) and each cap is the capacity for a project.  