Live SSAS Cube calculation behaves weird with intersect in MDX calculation

[precedence]

We have a calculation in a cube that sums up the difference of 2 measures at a specific level using intersect.  In Power BI if I drag that hierarchy in at a higher level it for some reasons only returns 1 record and filters the measure.  Even if I drag in the level of the hierarchy being used in the intersect it's still only a single value for the hierarchy.  I would expect to see the calculation as though it didn't have the intersect.  That is what I see in excel where it behaves as expected.  Power BI does not show the same thing as excel.  It's like Power BI is filtering behind the scene.  If I drag in the lowest level of the hierarchy that is being used in the intersect, it works as expected. 

MDX in the cube:

CREATE DYNAMIC SET CURRENTCUBE.[Existing Groupings]
AS existing [Product].[Product Hierarchy].[Material Grouping];

CREATE MEMBER CURRENTCUBE.[Measures].[Forecast Comparison]
AS sum(
INTERSECT(
descendants([Product].[Product Hierarchy].CurrentMember
,[Product].[Product Hierarchy].[Material Group],
SELF),
[Existing Groupings]),
abs([Measures].[Sales] - [Measures].[Forecast])
),

FORMAT_STRING = "#,##0;-#,##0", NON_EMPTY_BEHAVIOR = { [Sales], [Forecast] }, DISPLAY_FOLDER = 'Forecast', VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Measures';

[v-jiascu-msft]

Hi @precedence,

I can't reproduce. The info is limited.

1. Could you please post some snapshots of the result in Excel and in Power BI?

2. What is [Product Hierarchy]? Which one is the lowest level?

3. It would be great if you can provide the steps that I can use to reproduce.

Best Regards,

Dale

[precedence]

1.  attached screenshots

2.  It's 3 levels, top level being Product Category, Product grouping would be the 2nd level, Product would be the lowest level.  

3.  Once you have the MDX calculation set up in the cube you should see the issue.  All I had to do was drag Product Grouping onto rows and Forecast Comparison into values in Excel.  Then I created a table with the 2 fields in Power BI and got this weird result.

[precedence]

I just spoke to a coworker and apparently this is due to Power BI using DAX to query the cube instead of MDX.  Sets don't work properly in DAX.

[precedence]

Found a work around: 

Get rid of the dynamic set, and put it's definition right into the create member statement in the cube.

MDX:

CREATE DYNAMIC SET CURRENTCUBE.[Existing Groupings]
AS existing [Product].[Product Hierarchy].[Material Grouping];

CREATE MEMBER CURRENTCUBE.[Measures].[Forecast Comparison]
AS sum(
INTERSECT(
descendants([Product].[Product Hierarchy].CurrentMember
,[Product].[Product Hierarchy].[Material Group],
SELF),
existing [Product].[Product Hierarchy].[Material Grouping]),
abs([Measures].[Sales] - [Measures].[Forecast])
),

FORMAT_STRING = "#,##0;-#,##0", NON_EMPTY_BEHAVIOR = { [Sales], [Forecast] }, DISPLAY_FOLDER = 'Forecast', VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Measures';