Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Thanks.
Solved! Go to 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
Please try
NumberPOsMacro =
CALCULATE (
DISTINCTCOUNT ( PurchaseOrder_ProductEntity[PurchaseOrderID] ),
CROSSFILTER ( 'Entity Macro'[EntityID], 'Entity Micro'[EntityID], NONE ),
USERELATIONSHIP ( 'Product_Entity'[EntityID], 'Entity Macro'[EntityID] )
)
this didn't work, but
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |