powerbidaxdata-analysispowerbi-desktopmeasure

Powerbi Why does measure produce different result based on equivalent filters


I have a measure that counts the rows of a table ignoring the filtering on the variable post-secondary enrollment:

count_ignore_ps = CALCULATE(COUNTROWS(highschool_recent_year), REMOVEFILTERS(highschool_recent_year[ps_enr]))

Here is it used in a clustered column chart as the y axis. With the x axis being school and post-secondary enrollment. When used in this way the measure should be the total number of rows for the school. This works as intended on the left graph but does not on the right graph.

clustered column chart showing different values for equivalent filters

The left graph has filters:

The right graph has filters:

There are only three schools so no filter on school and the union of all the schools should be the same. The measure is producing different result.

I think this is because there are 10 race 1 students who are enrolled and 0 race 1 students not enrolled in school A. So for some reason the measure is not counting those students in the other condition.

Does anyone know what could be causing this issue or a solution?

Here is a link to the toy data: https://drive.google.com/file/d/143r4Nlmz819kdvaBCz6KFdkxAVP0R5ov/view?usp=sharing

Here is a link to powerbi file: https://drive.google.com/file/d/1fYP1Akv2wY2vIy9mRMrl3O26WpHwTtPk/view?usp=sharing


Solution

  • As David has noted, this looks like the "auto-exists" feature of Power BI. https://www.sqlbi.com/articles/understanding-dax-auto-exist/

    IMO, this is an unfortunate bug in the Power BI engine, and the only viable solution is to create "dimension tables" for attributes you need to filter.

    In this example, I've generated a "dimension table" called Races, connected it with a relationship to your table and replaced the existing page-level filter with Races[race].
    https://1drv.ms/u/s!AmLFDsG7h6JPiI0t_-0Wx__LjcW4EA?e=RVUrEW

    enter image description here

    That was enough to resolve your immediate challenge, but in your real model I would recommend you work through and create similar tables for the other attributes you are using for filters or as dimensions in your visuals.

    I prefer to build such tables using Power Query - relying on DAX is more fragile and it's easy to introduce circular dependency issues (another bug IMO, for another day ...).