Forum Discussion

Copper Contributor

Oct 31, 2021

Cubevalue and spilled range

I have a CUBERANKEDMEMBER() calculation which I found will spill nicely over a CUBESETCOUNT() sequence. I now want to spill a CUBEVALUE() calculation based on the generated list. It just throws a non...

excel

Formulas and Functions

SergeiBaklan

Diamond Contributor

Nov 01, 2021

samdthompson

I didn't test myself, but based on

Chris Webb's BI Blog: Excel Cube Functions, Dynamic Arrays And Lambdas, Part 1: Getting All The Items From A Set Chris Webb's BI Blog (crossjoin.co.uk)

Chris Webb's BI Blog: Excel Cube Functions, Dynamic Arrays And Lambdas, Part 2: Returning Tables Chris Webb's BI Blog (crossjoin.co.uk)

and comments to above, you can't do spill with CUBEVALUE(), only using MAKEARRAY()

samdthompson
Copper Contributor
Nov 01, 2021
Ah, cheers for that Sergei . I'll have to hold off then, I run insider channel for me but work limits me to the monthly enterprise so no makearray or lambda solution for this specific problem. I cant quite wrap my head around why I cant do it. I had got to the point that Chris mentions using cubesetcount to govern the spill range anyway so was surprised that cubemember wouldn't do the same. In my mind they are linked in function and form so should. I imagine that it just wasn't thought about when spill ranges were developed. Curses.