Hi Bob,

I can't make much sense of the screenshot because it's too small and when I make it bigger it's too blurry. I also can't see the relationships between the tables to understand why it's necessary to clarify the region name in the column of the left table in order to get the correct result in the right table.

Can you please share your file and give an explicit example of the figures you'd expect to see so I can follow the breadcrumbs to understand the issue.

Thanks,

Mynda

Thanks Mynda. Here's the file - its actually a stripped down version of a pretty old file, but it neatly illustrates the problem I have been encountering.

In the example, I have created an "on the fly" regional total using the following DAX formula:

RegionSales = CALCULATE(SUM(HOCL[ConstSales]), ALLEXCEPT(Geography,Geography[RegionName]))

If I have got the syntax correct, this should be calculating the sum of sales where the Region Name matches that for the constituency chosen, but only seems to work when I specify RegionName as a Column (as per LHS table). By way of example, Barnsley East constituency is part of Yorkshire & Humberside region, and for September 2018 the correct RegionSales figure should be 8,700,606 and not the national figure of 141,645,531.

Ultimately, I want to be able to create some measures based of RegionSales, eg three month moving average, % change on year ago etc, so its really important that RegionSales works in a free-standing way as intended.

Any further recommended reading on ALLEXCEPT, SUMX or RELATED functions much appreciated ...

Thanks as always
Bob

Hi Bob,

The measure requires the context in the table, hence why it works when you add the Region Name to the columns. However, you're in luck because there is a new Hierarchy Slicer (in preview) that will allow you to choose the constituency and it will also filter the Region Name without the need to have these fields in the table. 

You need to enable the preview feature in the File tab > Options and Settings  > Options > Preview features > Hierarchy Slicer. Then restart Power BI Desktop.

This will allow you to add multiple fields to the Slicer and your measure will now respect the RegionName filter. See attached.

Mynda

Thanks Mynda, its good to know I'm not doing anything wrong. 

This Preview feature doesn't seem to be available to me yet, but sounds just the job, so something to look out for.

Meanwhile, I have found something called "Hierarchy Slicer" on data visualisation MarketPlace - its PowerBI approved so may be the same tool.  I will give it a go ... 

Bob

You may need to download the latest version of Power BI to get the preview hierarchy slicer. If not, hopefully the one from the MarketPlace will do the job.