Forum Discussion
Average Value Unaffected By Filters
So after much head scratching I got this working by making the relationship between the Regions and Location tables bidirectional. The following code then worked:
Avg Audit Score - Regions =CALCULATE([Avg Audit Score],REMOVEFILTERS(Locations),VALUES(Regions))I now get the regional average for each location associated with a particular region, with the total for all regions equal to the the company group average. The values can still be filtered by columns in the Audits table, which is the desired outcome.I'm not sure if there is still a better way to code this without resorting to making the relationship bidirectional?
Community ChampionYour solution with the bidirectional relationship and the DAX code you provided seems to be working for your specific case. However, bidirectional relationships can have some performance implications, and it's generally a good practice to use them sparingly.
An alternative approach without the bidirectional relationship is to leverage the RELATED function to navigate from the Locations table to the Regions table. Assuming there's a direct relationship between Regions and Locations, and Locations and Audits, you can try the following DAX code:
Avg Audit Score - Regions =
CALCULATE(
[Avg Audit Score],
ALL(Locations[Location]),
VALUES(Regions[RegionName])
)
This code removes the filter on the Location table while keeping the filter from the Regions table. It assumes that there is a direct relationship between Locations and Regions, and Locations and Audits, and that the relationship between Regions and Locations is not bidirectional.
Remember, the effectiveness of DAX code often depends on the specific data model and relationships in your Power BI or other tabular model. It's always a good idea to test and evaluate the performance of different solutions with your specific dataset to determine the most efficient approach.
Thank you for your ongoing support here. Much appreciated and is really helping me think about filter context (which I'm still relatively new to).
The DAX you suggested:
Avg Audit Score - Regions =
CALCULATE(
[Avg Audit Score],
ALL(Locations[Location]),
VALUES(Regions[RegionName])
)
This only works when the cross filter between Regions and Locations is bidirectional. In a single direction relationship the value is the Company Group average. However, I think this makes sense I think for the following reason:
When I create a table visual with Location[Name] in the first column I am creating a filter context with that column. The Average Score - Region measure calculates the Audits[Score] for the Regions. However, in order to retreive the value of the Region[Name] from the Regions table, the Location[Name] needs to filter the 'Regions' table to get the Region[Name] from the 'Regions' table. This cannot happen in a single direction relationship from Region > Location, and so the relationship must be bidirectional.
For clarity my model is:
'Regions' ([RegionId]1>*[RegionId]) 'Locations' ([LocationId]1>*[LocationId]) 'Audits'
However, I think you might be on to something with the RELATED function as this I hope will overcome the issue of cross-filter direction. I somehow need to introduce this into the 'Average Score - Region' measure so that when the filter context including 'Locations'[Name] is created in the visual, the measure traverses back through the relationships to 'Regions' to get the single value from Regions, which then computes the average for the region but ignores the filter context from the visual.
Any suggestions on how to intorduce RELATED given the information above would be greatly appreciated. I'd love to mark this post 'Solved'!