Using DAX Measures from PowerBI model in Excel - drill down issue

cathryn13 — Fri, 03 Mar 2023 18:40:35 GMT

Hi Everyone -

I have a team that I cannot move to using Power BI visuals. They want to only use Excel and connect to the Power BI Models. But, we all know that the model table structure can look different in the Excel Pivot that it does in Power BI. If the measures are in the fact tables, they jump to top of the tables list in Pivot Table Fields section and separate themselves from the actual fact table with a sigma in front of the Fact table name to indicate the measures. In Power BI, the measures can sit either in a new table that you create specifically for measures OR the fact table that you create them from. They don't create their own table by default.

Σ[Fact Table Name 1]

Measure 1

Measure 2

Σ[Fact Table Name 2]

Measure 1

Measure 2

[Fact Table Name 1]

Field 1

Fields 2

Field 3

[Fact Table Name 2]

Field 1

Fields 2

Field 3

I have 2 questions around this...

1. Is there a way to combine the measures in the Power BI model from separate tables without creating their own table

2. I have the measures in their fact tables because in Excel I couldn't drill down to the details clicking on that measure in Excel if I didn't. Putting them in their own measure table I would get an error or no rows. (See Screenshot for error)

Does anyone have a solution to combine the measures into one table and still be able to drill through to details in Excel using a Power BI Model?

Thanks!

topic Using DAX Measures from PowerBI model in Excel - drill down issue in DAX Commands and Tips

Using DAX Measures from PowerBI model in Excel - drill down issue