Getting MdxScript calculation error in measure Report when refreshing PortfolioSlicer3.1-Reports

user1231

Hi Vidas,

I am a new user of Portfolio Slicer using office 2021. I am getting attached error message when refreshing PortfolioSlicer3.1-Reports.
Also attaching screenshot of my Symbol, Allocation and Symbol Allocation table.
Appreciate any help in resolving this error.

Thanks


Error message -


Allocation table -


Symbol table -


Symbol allocation table -

VidasMatelis

I am not sure why you are getting this error, but I suspect there is a bug in my code on how the file c:\PortfolioSlicer\PSData\SymbolAllocation.csv.
Can you post the content of your file here?

Also, after copying SymbolAllocation.csv file to this forum, you can try following:
Symbol Allocation table was supposed to be used just for Symbols that have split allocation. as your ^BSESN and ^NSEI symbols are allocated to a single allocatin, you can remove these 2 lines from SymbolAllocation table.

user1231

Attached SymbolAllocation.csv

Tried after removing both rows from Symbol allocation table. Still got the same error.

user1231

Looks like csv file is not getting attached. Here is the content

Symbol Allocation Percent
^BSESN INDIA 1
^NSEI INDIA 1
* Cash Cash 1
SHRIRAMPPS.NS INDIA 1
KARURVYSYA.NS INDIA 1

VidasMatelis

All looks good in that file. I cannot reproduce the problem on my end.
Would it be possible for you to send me:
1. Your Reports Excel workbook.
2. all files in c:\PortfolioSlicer\PSData folder

My email is posted on this page: http://www.portfolioslicer.com/contact.html
I am assuming you are just starting to test it and you do not have sensitive data in your workbook. But for your privacy, you can your transaction numbers, just make sure that after changing your error is reproducible.

Thanks!

VidasMatelis

Thanks for sending me your Excel workbook and data files.
On my PC I was able to refresh this workbook with your data without any problems.
Is it possible that you changed your workbook before sending it to me and that fixed the problem?
Please try to refresh workbook that I send you back. If you are getting error during refresh, could you please make a copy of your Excel workbook, from Overview tab delete "Allocation" report (right side) and then try refreshing again. By deleting that report you will remove that single measure that currently failing during the refresh and I want to see if you will get more errors with different error message.

user1231

I can confirm that when I use the report excel you sent me, it worked fine.
So to figure out what was wrong earlier, I tried with the one I sent you and got the same error again. Then I extracted a new one from zip and that didn't work as well.
While trying all this multiple times, one time it worked fine. Retracing my steps, I figured out if I select any cell from Allocation report in overview tab to right click an refresh it works and then it works every time after that.
If I select any cell in any other table, it gives me the same error till I use Allocation report to refresh.
Hope that helps narrow down the issue.

VidasMatelis

OK, can you please try this - instead of the right mouse click and "Refresh", can you please choose from the top menu Data, then "Refresh All" and "Refresh All".

user1231

It works fine with menu Data -> Refresh All -> Refresh All.

VidasMatelis

Great that we found the issue. Here is my explanation why it works one way and not the other.
When you do "Right mouse click"->"Refresh", Power pivot analyzes the report you did right mouse click and refreshes just tables that are used in this report. So it will not process ALL tables, just selected tables. Sometimes that impacted table identification is not correct or might not be complete. You might not notice this difference in some cases when you do any incremental refresh, but the first time you do refresh, data that is stored in PowerPivot memory and data your provided in the new file is very different and could cause unexpected errors.
Solution - always do Data -> Refresh All -> Refresh All.
I probably need to review and update my documentation for that. Thanks for reporting this!

hellohollo

Hi Vidas,

I'm getting the same error after trying to migrate from v2.4 to v3.1. Unfortunately refreshing through data/refresh hasn't helped. Do you have any idea what could possibly cause this for me?

Many thank

VidasMatelis

First of all, make sure that you are choosing the "Refresh All" and not just "refresh" option.
Second, can you please go to c:\PortfolioSlicer\PSData folder and check if there is a file error.txt? If there is one, it will give you idea on where the error is.

hellohollo

I made sure to choose "Refresh All" and the same error has happened. Unfortunately there is no error.txt file.

VidasMatelis

Can you please try following:
1. Attempt to refresh PS Reports-Light excel workbook. If not successful, continue to next steps.
2. Make a copy of your Source Excel workbook.
3. Delete the last half of transactions from source transaction table.
4. Save and close Excel workbook. Run UpdatePSDataFromExcel.bat
5. Open PS Reports LIGHT workbook and attempt to refresh. Attempt it 2 times. If fails continue to next step.
6. Repeat step 3,4,5 until you are able to refresh.
7. When refresh is successful, try adding a batch of transactions back to transactions table - but review transactions to see if there is anything suspicious. You will eventually identify one or few entries that cause problems.
8. After adding transactions do steps 4,5, 7.