Forum Discussion
How to stop the Switch Function from replacing a calculated value with a null value
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!
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
10 Replies
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
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!
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...
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
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.
Phil,
I sent you a link to the file in DropBox - thank you for your help!
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
