How to stop the Switch Function from replacing a calculated value with a null value

[rmbuchan]

Hello All, 

I could use help with the Switch ( TRUE () function. 

If I use a calculated value the switch result is null

However, if I hardcode a number in the switch instead of the calculated field, it works fine.

There is data in [Income] in a normal matrix column. Thank you for any help - I greatly appreciate it! 

[v-henryk-mstf]

Hi @rmbuchan ,

Whether the advice given by @PhilipTreacy  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[PhilipTreacy · Accepted solution]

Hi @rmbuchan 

Your [PL Actuals] measure is told to return the value of [Income] when the row index is 3.  

[Income] calculates [PL Values] when the Category is Income.  This is the problem.  The Category for rows with row index 3 is Total Income.  That's why you're getting a blank here.

You can fix the issue by modifying this line in the SWITCH to 

CRSindex = 3, CALCULATE([PL Values], 'RPT Income Statement'[Category] = "Total Income"),

Regards

Phil

[rmbuchan]

Phil, 

You are AMAZING - this solved the problem. Thank you so much for sticking with this issue and putting all the time in that you did! 

[PhilipTreacy]

No worries @rmbuchan , glad to help.

Phil

[PhilipTreacy]

Hi @rmbuchan 

Can you supply your PBIX file please.

Regards

Phil

[rmbuchan]

Phil, 

I sent you a link to the file in DropBox - thank you for your help! 

[PhilipTreacy]

Hi @rmbuchan 

It may very well produce values when charted but it's how the value is calculated when it's in the matrix that is the issue.

I presume [PL Values] is a measure?  How is that calculated?

Can you provide your file?  It woudl be easier to check if I have that.

Regards

Phil 

[rmbuchan]

Philip, 

Yes, PL Values is a measure. Here is the calculation: 

PL Values =

SUM( 'RPT Income Statement'[Act Amt])

The pbix file is a bit of mess right now because I've tried doing a lot of things to figure this issue out, but I can upload it if this calculation doesn't help.  

[PhilipTreacy]

Hi @rmbuchan 

How are you calculating [Income]?  That would seem to be the issue.

Regards

Phil

[rmbuchan]

Philip, 

Great question. I thought the same thing. I put the measure into a chart and it produces results. I also have the formula shown here: 

It just doesn't work in the Switch. Thanks for your assistance...