Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)+
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?
Solved! Go to Solution.
Hi @Anonymous ,
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
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.
Hi @Anonymous ,
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |