Selected Value, IF, IN, and multiple nested conditions

[Anonymous]

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?

[themistoklis · Accepted 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,

[Anonymous]

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

[themistoklis · Accepted 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,

[Anonymous]

Thank you ! Appreciate it, an embarrassingly simple fix.

Interested to know if IN works too, if anyone knows

[themistoklis · Accepted solution]

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)))

[Anonymous]

That works perfectly too, thank you! I like the IN option, as its tidier.

[themistoklis]

@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?