You might know that we can connect Excel to Power BI using “Analyze in Excel” feature.
If this is something new for you – check out following video (3 min)
By default, after clicking on “Analyze in Excel” you get Excel file with Pivot Table connected to Power BI.
There you can add rows, columns, slicers – everything as usual.
But what if Pivot Table is not what you need?
What if you have pixel perfect dashboard which you need to fill with CUBEVALUE formulas (or maybe you use Think Cell addin)?
And what if you want dropdown list of values in cell instead of slicers (or want filter to be generated via formula)?
Let’s check what we can do.
CUBEVALUE formulas
The easiest way to get CUBEVALUE formulas instead of Pivot Table is to convert Pivot Table to Formulas
That action deletes Pivot Table and replaces all its cells with functions CUBEMEMBER…
…and CUBEVALUE
As you may see, CUBEVALUE is referencing to a Slicer with countries and a cell with a CUBEMEMBER function.
Replace single value slicer with a cell
Everybody likes slicers, me too. However, in certain scenarios it is better to have a cell with value instead.
Why?
Value in a cell can be calculated via formula, slicer – only when manually clicked (or “complex” VBA).
Example: you have a monthly report, that shows YTD values.
Ideally: you open file, click refresh all (or even no need to click) – get everything refreshed, without manual clicks on slicers.
Sounds like a dream!
So let dream come true!
Consider two types of filtering by slicers
- Single value
- List of values
In both options we will have to use function CUBESET as analogue to a slicer.
Instead of slicer filter will be coming from a cell.
I gave name SET_COUNTRY to a cell with a country code
And used it in CUBESET function
Syntax of CUBESET: CUBESET(connection, set_expression, , [sort_order], [sort_by])
In my sample I’m using only two first arguments:
Connection – for this param I’m using the same string as in CUBEVALUE, which I’ve got when converted Pivot Table into formulas.
Set_expression – A text string of a set expression that results in a set of members or tuples. Set_expression can also be a cell reference to an Excel range that contains one or more members, tuples, or sets included in the set.
Basically, we have two ways of passing a value to a set_expression argument
- List of cells with CUBEMEMBER or CUBESET functions (as reference to a range(s))
-
Comma separated list of values in curly brackets passed as a string (chosen option)
List of cells usually cannot be dynamically generated unless you use VBA – this makes 1st option less dynamic.
As I’m a big fun of fully dynamic Excel data models, I want to get your attention to the second option.
I used value
“{” & “[Sales Org].[Sales Org Country].&[” & SET_COUNTRY & “]” & “}”
Firstly, curly brackets, as in Power Query, say that whatever is inside is a list. In this particular case, it is just one value.
Secondly, to use this way you need to know “path” / “coordinates” of the value in the Data Model. Path includes table, field and value separated with a dot symbol: [table].[field].&[value].
Parts of my formula:
[Sales Org] – table
[Sales Org Country] – field/column
“[” & SET_COUNTRY & “]” = [AU] – value stored in column ‘Sales Org Country’
Last piece is dynamic, as it is a reference to another cell, which can be entered manually / selected from dropdown list / generated by another formula.
Pretty easy so far.
Replace multiple values slicer with a cell
I’ll repeat description of example used in previous section
Example: you have a monthly report, that shows YTD values.
Ideally: you open file, get everything refreshed without interaction required from user, then save and close.
When workbook designed in such way it becomes very easy to refresh it via VBA or VBscript, or using my Reports Controller (aka Power Refresh).
For mentioned YTD scenario we will need
Report Date – calculated from TODAY()
List of YearMonth values JAN to [Report Date Month] to cover YTD period
Consider YTD as “Jan to [end of prior month]”. Calculation of the end of previous month in Excel can be easily done with EOMONTH function
For the list of YTD YearMonth values I’m using following table with 12 rows
Each cell of this table contains a formula. Values are calculated relatively to REPORT_DATE
For Date column
=DATE( YEAR(REPORT_DATE), ROW( B12 ) – ROW( tblYearMonth[#Headers] ), 1 )
For YearMonth column
=YEAR( [@Date] ) * 100 + MONTH( [@Date] )
For Relevant column
=IF( [@Date] <= REPORT_DATE, [@YearMonth], “” )
These formulas helps me to avoid manual update of this table each year, and automatically include each new YearMonth.
Having such table I can generate a string for CUBESET argument, by using TEXTJOIN function
Formula I’m using
=”{” & “[Calendar].[YearMonth].&[” & TEXTJOIN( “]” & “,” & “[Calendar].[YearMonth].&[“, TRUE, tblYearMonth[Relevant] ) & “]}”
The tricky part of it is a complex Delimiter – first argument of TEXTJOIN.
Usually delimiter is a one-char symbol like comma of semicolon, but in this case
“]” & “,” & “[Calendar].[YearMonth].&[“
This is because in CUBESET function each element of set_expression argument should point to a particular “path” in the Data Model.
E.g. “[Calendar].[YearMonth].&[201801]”
Once such string is ready, we can simply reference to it in a CUBESET function
You might noticed that the cell with CUBESET function doesn’t show any value in it. However, it is not blank. We can check how many elements in CUBESET by using CUBESETCOUNT function.
Using CUBESET in CUBEVALUE
Finally, when we have all our CUBESET-s defined, we can use them in CUBEVALUE function.
How does it work?
Or, what will happen when I open file in next month?
Same as usually, Excel will calculate chain of formulas.
Simplified process:
Firstly, Excel gets value of the Report Date, which is relative to the date of opening file.
Then Excel calculates YearMonth table, and then cubeset with YTD YearMonth.
At some point Excel gets value for Country Cubeset.
And finally, all dependent cells – all cells with CUBEVALUE and the rest.
Same approach can be used with Power Pivot, SSAS, OLAP cubes.
You can find official documentation on CUBESET here.
Couple of good articles to learn more about CUBESET
https://dataonwheels.wordpress.com/2015/06/16/excel-bi-tip-27-cubeset-and-cubesetcount-functions/