Model relationship not working in analyze in excel

[Anonymous]

I have two queries in my powerbi report. 

One is inventory information and the other is product heirarchy table(dimension table). I have created one to many join between both these tables based on the common field item number with cross filtering in both direction.

When i use analyze in excel feature and fetch a field from product heirarchy table against any item it gives me multiple product heirarchies when every item is actually having only one product heriarchies.
Please see below screenshots:

In Powerbi Service it working fine:

Please let me know why is this happening or is this a limitation of analyze in excel feature and how can we resolve it.

[Icey · Accepted solution]

Hi @Anonymous ,

Please check if your data model has met one requirement for using Analyze in Excel:

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

Please put one measure into "Value" field and test it again.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

Thanks for the tip Icey.

Is there an alternative to having to create measures within Power BI to be able to use values in Excel?  For example could we create an MDX calculated measure within Excel itself to handle the conversion of the numeric fields into something useable in Excel.  I've found having to create additional measures in Power BI just to do some quick analysis within Excel seems a long way around, and wondered if there is a simpler MDX approach instead?  I've tried looking but haven't yet found a way to do this - should I just give up on the idea do you think? 🙂