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.
Hi all, I'm not having much luck with my Google Fu today. I'm trying to create a measure based on a Month slicer value, that checks what month is selected and returns a value (the number of hours in that month) that can be used in another measure to calculate Utilisation %.
I've tried the following two options without much luck:
Potential Berth Hours Available =
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] = "January", 744,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] = "February", 672,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] = "March", 744,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] = "April", 720,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] = "May", 744,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] = "June", 720,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] = "July", 744,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] = "August", 744,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] = "September", 720,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] = "October", 744,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] = "November", 720,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] = "December", 744,
0)))))))
Potential Berth Hours Available =
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] In { "January", "March", "May", "July", "August", "October", "December"}; 744;
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] In { "February"}; 672;
IF(SELECTEDVALUE('Berth Utilisation'[Month Name] In { "April", "June", "September", "November"};720;
0
I thought you could use IN, but am unsure. Can anyone assist, please?
Solved! Go to Solution.
@Anonymous
You forgot a closing bracket right after [Month Name] .
So your formula should be:
IF(SELECTEDVALUE('Berth Utilisation'[Month Name]) = "January", 744,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name]) = "February", 672,
IN should work as well if you add the closing bracket to formula and replace semicolons with commas.
Potential Berth Hours Available =
IF(SELECTEDVALUE('Berth Utilisation'[Month Name]) In { "January", "March", "May", "July", "August", "October", "December"}, 744,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name]) = "February", 672,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name]) In { "April", "June", "September", "November"}, 720,
0)))
I should clarify the errors I am getting. The first formula attempt returns the error "Too many arguments were passed to the SELECTEDVALUE function. The maximum argument count for the function is 2."
The second formula didn't like the semi-colons so I replaced them with commas and I got the same error, and an incorrect parameter result too
@Anonymous
You forgot a closing bracket right after [Month Name] .
So your formula should be:
IF(SELECTEDVALUE('Berth Utilisation'[Month Name]) = "January", 744,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name]) = "February", 672,
Thank you ! Appreciate it, an embarrassingly simple fix.
Interested to know if IN works too, if anyone knows
IN should work as well if you add the closing bracket to formula and replace semicolons with commas.
Potential Berth Hours Available =
IF(SELECTEDVALUE('Berth Utilisation'[Month Name]) In { "January", "March", "May", "July", "August", "October", "December"}, 744,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name]) = "February", 672,
IF(SELECTEDVALUE('Berth Utilisation'[Month Name]) In { "April", "June", "September", "November"}, 720,
0)))
That works perfectly too, thank you! I like the IN option, as its tidier.
@Anonymous
So the problem that you are experiencing is that the formulas do not return anything?
Or they return someting different from what you expected?
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |