cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

YearCapacity
2020100
2021

120

2022150

 

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

Project #Capacity Addition
110
220
330

 

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:

YearCapacityNew Capacity
2020100100
2021

120

130

2022150160

 

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)+
IF(SELECTEDVALUE('Project (2)'[Selected Year],10000)<='Sum Table'[Year],'Sum Table'[Capacity]+LOOKUPVALUE('Project Data'[Total Capacity Added],'Project Data'[Roadmap Projects],"2"),0)+
IF(SELECTEDVALUE('Project (3)'[Selected Year],10000)<='Sum Table'[Year],'Sum Table'[Capacity]+LOOKUPVALUE('Project Data'[Total Capacity Added],'Project Data'[Roadmap Projects],"3"),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
Community Support

Hi @CazCzw ,

Please check whether the below measure is what you want.

New Capacity = 
VAR _selproj =
    SELECTEDVALUE ( 'Projects'[Project #] )
VAR _additionalcap =
    CALCULATE (
        MAX ( 'Projects'[Capacity Addition] ),
        FILTER ( 'Projects', 'Projects'[Project #] = _selproj )
    )
RETURN
    MAX ( 'Capacity'[Capacity] ) + _additionalcap

ten capacities.JPG

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.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Hi @CazCzw ,

Please check whether the below measure is what you want.

New Capacity = 
VAR _selproj =
    SELECTEDVALUE ( 'Projects'[Project #] )
VAR _additionalcap =
    CALCULATE (
        MAX ( 'Projects'[Capacity Addition] ),
        FILTER ( 'Projects', 'Projects'[Project #] = _selproj )
    )
RETURN
    MAX ( 'Capacity'[Capacity] ) + _additionalcap

ten capacities.JPG

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.

View solution in original post

Highlighted

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. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors