Visualization data changes when using count filtering

[Sharkybu]

Hello again to the brilliant minds here.
 This is an example to the table I have (I apologize for the spelling error, i built the file in a hurry)

My needed parameters to filter:

Before 2020 - eliminating Project 6 (Done by basic visualization parameter)

Must have internal hires- eliminating Project 2 (Done by a mix of DAX columns and visualization filtering)

Project that a certain facility is a part of- chosen by user (Done by creating a new table, DAX column and a measure that is filtered) 

Result when selecting West

And then I need two bar charts
One shows how many projects each internal person has (filtering the result table)

One shows amount of project every department from chosen facility has (creating a second facility measure to filter by)

I'm able to get the results I want but when I try to 'count' the  "id" column, the results change.

I can't understand why it's doing that.
Please save me.

(It won't let me attach the actual data file)

Thank you.

[v-linyulu-msft · Accepted solution]

Hi, @Sharkybu 
Thanks for reaching out to the Microsoft fabric community forum.

Regarding the issue you raised, my solution is as follows:

Based on my tests, the issue you are encountering is mainly due to the count aggregation method's calculation process not being directly affected by filters. In other words, when you use the aggregation method on a field, the filters will only determine whether the row values are displayed, but they will not affect the calculation result itself.

If you want to get the desired result directly, I recommend using the following measure:

MEASURE = 
VAR cc1 =
    SELECTEDVALUE ( 'Table 2'[Facility] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER (
            'Table',
            'Table'[internal hire] = "Yes"
                && CONTAINSSTRING ( 'Table'[Project facility], cc1 )
        )
    )

The final result is as follows:

Please note that my visual object does not have any filters set.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[Sharkybu]

Thank you, thank you, thank you!!!

[v-linyulu-msft]

Hi, @Sharkybu 
I am glad that my answer could help you, and I appreciate you accepting my answer as the solution. This recognition of my efforts also helps others in the community with similar issues find solutions more quickly.

If you encounter any problems in the future, feel free to share them again.

Have a great day!

Best Regards,

Leroy Lu