Selecting which relationships and filters to use with DAX

[TheLoonies]

Hi,

I want to create a report which allows the user to select some group from within the dataset and then a subgroup from within that group and use aggregations of the group and subgroup to create measures

for example:

If the data is fresh produce, then the user can select "fruit" as the group and "oranges" as the subgroup and then it might calculate (sales of oranges )/ (sales of fruit); the percentage of fruit sales which were oranges

or they could pick: "fruit and vegetables" as the group and "oranges" as the subgroup

or "fruit and vegetables" as the group and "fruit" as the subgroup. In each example I want to have returned the subgroup as a percentage of the group.

I thought it was important that the user can only select subgroups which are contained within the group so I set up relationships as follows:

This allows the slicers in the report to function sufficiently close to how I want.

Now I want to create measures such as:

NumberPOsMacro = CALCULATE(

    DISTINCTCOUNT(PurchaseOrder_ProductEntity[PurchaseOrderID]),

    USERELATIONSHIP('Product_Entity'[EntityID], 'Entity Macro'[EntityID])

    )

The issue is that this doesn't work, it doesn't appear to use the correct relationship.

Manually modifying the relationships sometimes works:

This makes sense, in the case where it fails the filters from the subgroup are still applying while in the case where it works they don't.

I assume that it doesn't work because USERELATIONSHIP is disabling the relationships in a way which matches the fail case.

adding REMOVEFILTERS as so:

NumberPOsMacro = CALCULATE(

    DISTINCTCOUNT(PurchaseOrder_ProductEntity[PurchaseOrderID]),

    REMOVEFILTERS('Entity Micro'),

    USERELATIONSHIP('Product_Entity'[EntityID], 'Entity Macro'[EntityID])

    )

Also doesn't work, it successfully removes the filters but it also disables the USERELATIONSHIP.

How can I get the measures to use the relationships as configured in the image above under works? If that's not adviseable, how can I get this report to function as intended in an alternative way?

Here is the entire model for reference:

Thanks.

[tamerj1 · Accepted solution]

@TheLoonies 
Please try

NumberPOsMacro =
CALCULATE (
    DISTINCTCOUNT ( PurchaseOrder_ProductEntity[PurchaseOrderID] ),
    CROSSFILTER ( 'Entity Macro'[EntityID], 'Entity Micro'[EntityID], NONE ),
    USERELATIONSHIP ( 'Product_Entity'[EntityID], 'Entity Macro'[EntityID] )
)

[TheLoonies]

this didn't work, but 

NumberPOsMacro = CALCULATE (

    DISTINCTCOUNT ( PurchaseOrder_ProductEntity[PurchaseOrderID] ),

    CROSSFILTER ( 'Entity Micro'[EntityID], 'Product_Entity'[EntityID], NONE ),

    USERELATIONSHIP ( 'Product_Entity'[EntityID], 'Entity Macro'[EntityID] )

)

did.

thanks so much for your help!