Measures affecting filter behavior

[Believer]

You should be able to download the zip containing the PBIX and CSV files from here.

There are 5 rows displayed in the report sample below regardless of the values selected in the "Type" filter.  Because of the way the measures are defined, the values within the table may change, but not the actual rows.  If I remove the measures "Cust DBs Tot" and "Cust Descr Total" then the filter behaves as expected, removing the appropriate rows.  Can someone explain this filter behavior?

The tables and their relationship:

Some sample data (there's more in the PBIX):

List:

Name	Type
fA	F
fC2	F
fM1	F
fM2	F
pH1	H
pH8	H

Codes:

Name	Code	Description	Source	ACount	Valid
fC2	0	0	NULL	0	No
fM2	0	0	Manual	1	No
fM2	0	0	NSC	41	No
pH8	17726	ACM	NULL	0	Yes
fA	<ADA>	ADA	NULL	0	Yes
fM1	<ADA>	ADA	NULL	0	Yes
pH1	<ADA>	ADA	NULL	0	Yes

Of the 3 codes listed, “0” is only aligned with Type “F”, “17726” with “H”, and “<ADA>” with both “F” and “H”.

Here are the calculations:

Cust DBs Tot = CALCULATE(DISTINCTCOUNT('Codes'[Name]),ALLEXCEPT('Codes','Codes'[Source],'Codes'[Code]))

Cust Descr DB Valid = CALCULATE(DISTINCTCOUNT('Codes'[Name]),ALLEXCEPT('Codes','Codes'[Source],'Codes'[Code]),'Codes'[Valid]="Yes")

Cust Descr Total = CALCULATE(DISTINCTCOUNT('Codes'[Description]),ALLEXCEPT('Codes','Codes'[Source],'Codes'[Code]))

Cust Descr Valid = CALCULATE(DISTINCTCOUNT('Codes'[Description]),ALLEXCEPT('Codes','Codes'[Source],'Codes'[Code]),'Codes'[Valid]="Yes")

The intent behind these was to ignore any report filters on “Source” and “Code” when the values were displayed, or to force only Valid=”Yes” values.

For this data, the default view with no filters is:

Filtered on Type=F shows…

…however I would have expected “17726” to get dropped since it only has 1 Name “pH8” which is type “H”.

Filtered on Type H shows…

…and I would similarly have expected Code “0” to get dropped.

While I was writing this up I discovered that I can add a filter to not show entries where “ACount” is blank, but it feels like a bit of a hack, and I would still like to know why I am seeing this behavior so I can better understand relationships, filtering, and measures.

[V-lianl-msft]

Hi @Believer ,

This is due to the ALLEXCEPT function, which ignores the filter for calculation.

Try measure like below:

Cust DBs Tot = var current_source = MAX(Codes[Source])
var current_code = MAX(Codes[Code])
return CALCULATE(DISTINCTCOUNT('Codes'[Name]),FILTER(ALLSELECTED(Codes),Codes[Code]=current_code&&Codes[Source]=current_source))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Believer]

@V-lianl-msft I think you misunderstand my question.  My calculations are exactly what I expect.  The behavior I do not expect is that the "F" records aren't dropped from the list of data like they should be, resulting in fewer rows displayed.  Instead, the records stay on the screen with blank results.  So it's not a calc issue, but a filter behavior issue... but only when the calculations I mentioned are in the view.  If they're not there, the filter behaves as expected.  I actually added two more columns to the view Min(Type) and Max(Type).  Since there are only 2 types, this works well to show something else unusual... With non-filtered data, min/max(Type) are F and H on each row wherever apprpriate for the data.  Of the offensive measure are not in the view, the filtering on F or H results in only F or H min/max values, as expected.  But when the offending measure(s) are added back in... every row shows F and H no matter what.  This demonstrates that Power BI thinks there are F and H in each row, and why the rows aren't removed by the filter.  By why does Power BI think this?  (Hopefully all that makes sense...)