Measure returning rows from unrelated projects when filtering

[lcfaria]

Hi everyone,

I need help from the community and I attached the pbix file in the end of this post.

I have a data model with the following tables and relationships:

• f_data fact table

• d_projects dimension

• d_calendar dimension

• d_EntrySource dimension

• d_Visibility dimension

Relationships are set as follows:

• d_projects → f_data (one-to-many)

• d_calendar → f_data (one-to-many)

• d_EntrySource → f_data (one-to-many)

• d_Visibility → f_data (one-to-many)

I created a very simple measure:

When I place this measure in a table visual with project_id and apply a filter to show only project ABC0007, the visual still returns multiple projects (example in screenshots).

• Without the measure, the filter works as expected (only ABC0007).

• With the measure, additional projects that should not be in the filter context appear.

Questions:

• Why does adding a constant measure like "_Test = 1" make other projects appear in the visual?

• What’s the correct way to keep the filter context applied only to the selected project when using this type of measure?

You can find the pbix file in this link: Sample_FilterContext.pbix

Thanks in advance!

[danextian · Accepted solution]

Hi @lcfaria 

Adding  a constant value to a result of a measure is similar to forcing DAXto show 0 when blank. Power BI's VertiPaq engine improves performance by skipping unnecessary calculations when handling blank (null) values. However, if a measure forces zeroes or any other value, the formula engine performs a full cross-join of the dimension tables in the visual, evaluating each row explicitly. This increases query execution time instead of scanning only those rows where records for the combination of the dimension attributes really exist. It can also cause unexpected data points to appear. For example product X, which was terminated in 2013, may still show up for a selected period like February 25, but with zero or other constant values. This isn't the case if all columns are coming from the same table as there's no crossjoin of dimensions from multiple tables that's happening.

Why not instead count the distinct project id in your fact table?

[lcfaria]

Thank you very much for your explanation @danextian, I really appreciate that. Very helpful 👊