• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

December 3, 2020   Self-Service BI

When you connect to a Power BI Dataset from Power BI desktop you might have noticed that you can see and use hidden measures and columns in the dataset.

113020 2035 usehiddenme1 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

But the hidden fields cannot be seen if you browse the dataset in Excel.

113020 2035 usehiddenme2 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

But that does not mean that you cannot use the fields in Excel – and here is how you can do it.

Using VBA

You can use VBA by creating a macro

113020 2035 usehiddenme3 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

The code will add the field AddressLine1 from the DImReseller dimension as a Rowfield if the active cell contains a pivotable.

113020 2035 usehiddenme4 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table
Sub AddField()
    Dim pv As PivotTable
        Set pv = ActiveCell.PivotTable
        pv.CubeFields("[DimReseller].[AddressLine1]").Orientation = xlRowField
End Sub

If you want to add a measure/value to the pivotable you need to set change the Orientation property to xlDataFields

113020 2035 usehiddenme5 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

This means that we now have added two hidden fields from the dataset

113020 2035 usehiddenme6 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

Add hidden measures using OLAP Tools

You can also add hidden measures using the OLAP Tools and MDX Calculated Measure

113020 2035 usehiddenme7 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

Simply create a new calculated measure by referencing the hidden measure in the MDX

113020 2035 usehiddenme8 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

This will add a calculated Measure to the measure group you selected

113020 2035 usehiddenme9 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

And you can add that to your pivotable

113020 2035 usehiddenme10 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

Referencing hidden items using CUBE functions

Notice that you can also reference the hidden measures using CUBE functions

113020 2035 usehiddenme11 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

Simply specify the name of the measure as the member expression in this case as “[Measures].[Sales Profit]”

You can also refer to members from hidden fields using the CUBEMEMBER functions

113020 2035 usehiddenme12 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

Hope this can help you too.

Power On!

Let’s block ads! (Why?)

Erik Svensen – Blog about Power BI, Power Apps, Power Query

#powerbi, Dataset, Excel, from, Hidden, Measures, Members, Pivot, table
  • Recent Posts

    • Someone’s having surgery
    • C’mon hooman
    • Build and Release Pipelines for Azure Resources (Logic Apps and Azure Functions)
    • Database version control: Getting started with Flyway
    • Support CRM with New Dynamics 365 Field Service Mobile App
  • Categories

  • Archives

    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited