Many of our clients have asked if it’s possible to present hierarchies in Power BI more like they appear in Essbase. They invariably want something like the above slicer and matrix. Power BI’s flexibility really shines in this area.
Our Essbase connectors pull in metadata, as well as data. Specifically, you can get a parent/child representation of a given dimension that also contains other metadata, like the following:
We can then use this path to flatten our parent/child relationship. Another DAX function, PATHITEM, can be used to create a new column for each level in the path. This is a manual process, but it only needs to be done for the initial setup:
Armed with our paths/levels, we can create a hierarchical slicer and a matrix report like those in the first screenshot of this post. We can also use the Acterys Matrix Light visualization control to simulate a grid that looks more like Essbase/Planning: