Loading BI data model in Excel

[PowerBITestingG]

Hi all,

Whenever I load my data model into excel to create a pivot table, it seems the relationships from the model are not working. 

Like if I filter by year in the date table, the linked sales table doesnt filter, it just shows everything.

It only works if I use the fields from the same table

Am I missing something?

Thanks in advance

[Anonymous]

Hi @PowerBITestingG ,

There is a limit.

• Excel PivotTables don't support drag-and-drop aggregation of numeric fields. Your dataset in Power BI must have pre-defined measures. Read about creating measures.

Build refreshable Excel reports with Power BI data - Power BI | Microsoft Docs

My guess is that you put both fields into the value.

You can create a measure and put it in the value instead of the column. Measures will be automatically summarized within the row context.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[PowerBITestingG]

No, I didnt even need numeric fields

I was trying to leverage the BI relationships to make a simple pivot just with rows. Like Customer Name, Product bought, etc

I thought that when you imported the data model into excel from BI, that the relationships would hold.

But they didnt, so instead I ended up merging all the tables together.