Filter Excel Function CUBEVALUE Connected to Power BI

Ivan Bondarenko November 26, 2018 No Comments

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?

(more…)

Sort months by first letter in PowerPivot and Power BI

Ivan Bondarenko October 10, 2016 2 Comments

This post is a logical continuation of previous one where I described how to place measures in rows in Power BI table visual.

Here I again talk about trend tables that looks like

It displays several measures in a matrix with months, so we may see development of each measure.

Data viz gurus will say that such data must be on chart.

Right, but sometimes people want to see figures in tables and … nothing else.

One of the problems here is to display month first letters in right order. (more…)

Unfold Child-Parent hierarchy in Power Query

Ivan Bondarenko September 9, 2016 3 Comments

In this post I consider two types of hierarchies

First table defines typical hierarchy of companies.

Usually, such hierarchy is used for financial reports to group key figures.

Most probably, we know number of hierarchy levels upfront, but not always.

Task: Expand hierarchy, automatically detect quantity of levels in resulting table using Power Query (Get & Transform) / Power BI

Stay close to scenario described in pattern: http://www.daxpatterns.com/parent-child-hierarchies/

Goal:

Second table defines substitution of products.

Case: sometimes we required to get sales history of all predecessor products and group result on Actual Product. There can be multiple substitutions, we never know amount.

Task: In this case we need to find the latest successor, Actual Product for each product in other words.

Goal:

(more…)

SAP ByDesign + Power BI = cloud friends

Ivan Bondarenko April 14, 2016 No Comments

In this post you will find an idea how to pull data from SAP ByDesign directly to Power BI. This is a totally self-service solution, which can be built if you have literally nothing. Only you, Excel, free license of Power BI and SAP ByDesign tenant(s).

Samples based on data from test tenants that were opened for participants of openSAP course “Reporting with SAP Business ByDesign“.

Couple of words about SAP ByD for those who are not aware what is this. Cloud ERP system, has own real-time analytics engine, quite powerful, but poor. Poor calculation capabilities, poor visualization capabilities. Handles only basic things, obvious calculations.

Moreover, if you have several SAP ByDesign systems in responsibility you most probably have to consolidate data somewhere. Build sub-regional, regional reports etc.

(more…)

Xlsb vs Xlsx PowerPivot model

Ivan Bondarenko January 7, 2016 No Comments

I bet over the Internet can be found number of posts with comparison of workbook size with different formats. However, nothing is better than own data :).

Assume that we have an Excel file with quite big amount of data

~250k rows

As we already know XLSB format makes file size much smaller than XLSX

But what if we load data into Data Model of empty workbook – will it be smaller than xlsb?

To check this I created new Excel workbook, went to PowerPivot tab and opened Data Model window

I need import from Excel

It is stored under “From Other Source” – scroll down to the end

Browse for my source file (I took xlsx as a source)

On the next step I can make “Preview and Filter” to exclude unnecessary data.

You may see checkboxes and usual dropdown filter buttons on columns – like in Excel I can select what I need in filter menu

For this “size challenge” I should take everything. So simply press “Finish” and wait while PowerPivot imports data.

After few seconds we may see result. All rows were imported – no errors.

Result in PowerPivot window

Save this file as xlsx and xlsb

Champion still is XLSB with simple table. PP version size is 11% greater.

And we may see minimal difference between PP versions in different formats. xlsx and xlsb have almost the same size.

And what interesting – xlsb is slightly bigger than xlsx in this case.

Let’s make one more test. Delete text column from initial file. It contains comments for transactions.

This column contains huge number of unique values, in addition it is text – the most heaviest data type.

I repeated same actions with file without text column and result is opposite

PP version has 11% less size than initial “xlsb without text column” and 4.5 times less than initial Without text column.xlsx.

Thoughts that come in my mind after all

Choice where to store data, in Excel table or in PowerPivot, depends on data. There are pros and cons. In the end, PowerPivot don’t lose. Usually data models contain more numbers and less text.

Work with 250k rows in Excel so slow that close to impossible, several SUMIFs will raise a “lack of memory error”. Instead of SUMIFS PivotTables can be used and then GETPIVOTDATA function… All of this currently “old style” modelling that helped us a lot before PowerPivot came – should stay in the past.

What if we want analyze R12M and take 12 files? We will have >1M rows, that is not supported by Excel sheets, BUT PowerPivot supports and still has quick performance of calculation.

Happy modelling in 2016!