Possible Power BI bug - Incorrect ALL() measure calculation when using combination of filters

[GavinH]

I have discovered what appears to be a bug in Power BI and am hoping for some help bringing it to the attention of Microsoft's Quality Assurance or the Power BI Development team. It concerns some very odd interactions between: 

1. A measure that uses the ALL() function,

2. A filter applied to a variable used as a column header in a matrix visual selecting more than one value (but not all), and

3. Filters applied to other columns in the same data table that select more than one value (but not all)

I have created a sample .pbix file that can be accessed at the following github repository: https://github.com/ghammel/PBIBug/blob/main/README.md 

In the file, I use a calculated column [Fiscal Year] as the column value for a matrix visual. The column works fine and was created using the following DAX: 

Fiscal Year = 
var CurrentYr = Sheet1[Year]
var CurrentQtr = Sheet1[Quarter]
var RevisedYr = IF(CurrentQtr=1, CurrentYr-1, CurrentYr)
var FollowingYr = RIGHT(FORMAT(RevisedYr+1, "General Number"), 2)
return
FORMAT(RevisedYr, "General Number")&"-"&FollowingYr

The problems begin when I use that column in combination with the following three criteria in a matrix visual: 

1. A measure that uses the ALL() function - the example file has two of these, [Company share] and [Denominator] 

Company share = DIVIDE([Total amount], CALCULATE([Total amount], ALL(Sheet1[Company], Sheet1[Division])), BLANK())

Denominator = CALCULATE([Total amount], ALL(Sheet1[Company], Sheet1[Division]))

2. A filter applied to the [Fiscal Year] column - in the example, I exclude 2018-19 and 2019-20

3. A filter applied to another column in the same data table selecting more than one (but not all) values - in the example, I use [Region] and select Regions "111" and "113"

Here is what I should be getting: 

Note that all of the [Denominator] values are identical, ignoring the [Company] context entirely. 

However, when I meet all of the conditions mentioned above, the results are very different (and incorrect): 

Now the [Denominator] value is inconsistent, which results in [Company share] calculating incorrectly (totalling more than 100%). 

What appears to be happening is this: in the source table, all [Company] values have rows with [Region] values of "113". However, three [Company] values - companies "C", "F", and "I" - have no rows with [Region] values of "111". For some reason, when the measure calculates the denominator for those three companies, which should contain the [Total amount] for both "111" and "113", it is only including the total for Region "113". Somehow the [Company] value is affecting which [Region] values get included in the calculation. 

This same result occurs if I use a different variable as my column variable AND directly filter the values in that variable. (I originally thought this was confined to calculated columns, but it seems not to be the case.) For example, I created an alternate fiscal year column in Power Query called [FiscalYrQuery] and again filtered out values "2018-19" and "2019-20". The results were again incorrect: 

Has anyone experienced similar issues? Is there a way to bring this to the attention of Power BI Quality Assurance or Development teams? I have already tried Power BI User Support with no success, and I can find no good way to report bugs with Power BI.  

Any help or additional information would be much appreciated.

[HotChilli · Accepted solution]

Possibly an auto-exist issue. I don't have time to investigate this but that would be my initial suspicion on seeing a filtering issue in a one-table model.

[GavinH]

So does auto-exist not work the same way in Excel Power Pivot? Because the existing structure works fine when I do the exact same thing using Power Pivot in Excel.

[GavinH]

I had never heard of auto-exist, but after looking it up, that appears to be the explanation. 

Thank you!

[Greg_Deckler]

@GavinH Interesting, might take a look. You can submit bugs here: Issues - Microsoft Fabric Community