In the PowerPivot environment of Excel, I have loaded a table called "Text" containing two columns: timestamp [Time] and text string [Report]. I then defined a custom DAX measure as:
GetText:=IF(HASONEVALUE('Text'[Report]),VALUES('Text'[Report]),BLANK())
I use that to display the text of the column "Report" in Excel using:
CUBEVALUE("ThisWorkbookDataModel","[Text].[Time].["&E$3&"]","[Measures].[GetText]")
where cell E3 contains a time to use as a filter in this example.
This all works perfectly fine, except for when the length of the text to display in Excel exceeds more than 255 characters. In that case, it shows an unending string of "#####...".
Excel cells can display up to 32767 characters, so that shouldn't be the issue (if I look at the length of the text in PowerPivot, it's about 300-500 characters on the values that show the error). I do know that when using CUBEVALUE, the member_expression cannot exceed 255 character. In such case, you'd simply refer to a cell containing the longer input. But I'm dealing with the output that the custom measure returns - that shouldn't be limited like that.
Is it possible to display the full text as shown in PowerPivot in the Excel cell?
Turns out that the object that CUBEVALUE returns is just inherently limited to a length of 255 characters.
HOWEVER, if you simply convert the output to something that can be longer, it works perfectly fine. For example, just convert the CUBEVALUE to text with VALUETOTEXT():
VALUETOTEXT(CUBEVALUE("ThisWorkbookDataModel","[Text].[Time].["&E$3&"]","[Measures].[GetText]"))