CALCULATE/FILTER measure Gives a different result to table and filter

[danjswade]

Hi

I have two tables: 1 is an activity table and 1 is a history table. They are linked by unique GUIDs. If I display a visual and sum a column from the activity table and apply a filter, I get the correct data (i.e. returns 3 entries). If I use the following measure, it returns only 2 (essentially ignores one fo them):

calculate(

            sum(BI_Fact_Activity[VacancyAddedCount]),

            filter(

                BI_Vacancy,

                BI_Vacancy[eDate] = DATE(2222,02,22)

            )
)

Ultimately, I want to sum the AddCount column where the relevant GUID link the BI_Vacancy table has an eDate of 22/02/2222.

[danjswade · Accepted solution]

I'd respond in case anyone encounters the same issue. I fixed it by wrapping the filter table in ALL(), can only assume SOMETHING was impacting on the data being filtered, but this did the trick:

calculate(

            sum(BI_Fact_Activity[VacancyAddedCount]),

            filter(

                ALL(BI_Vacancy),

                BI_Vacancy[eDate] = DATE(2222,02,22)

            )
)

[jolind1996]

Based on the information you provided, it seems that the issue you are facing is related to the difference between the filter context in the visual and the filter context in the measure. The CALCULATE function allows simple filters which will replace the existing filter context. In your example, CALCULATE will compute the measure using the existing filter context, except that it removes any existing filter context for the column specified in the FILTER function and replaces it with the new filter condition.

One possible solution could be to check the filter context in the visual and make sure it is the same as the one specified in the FILTER function of the measure. You could also try to modify the measure to include additional filter conditions to match the filter context in the visual.

Here is an example of a DAX code that you can use to modify the measure to include additional filter conditions to match the filter context in the visual:

Measure =
CALCULATE (
    SUM ( BI_Fact_Activity[VacancyAddedCount] ),
    FILTER (
        BI_Vacancy,
        BI_Vacancy[eDate] = DATE ( 2222, 02, 22 )
    ),
    FILTER (
        RELATEDTABLE ( BI_Fact_Activity ),
        BI_Fact_Activity[GUID] = RELATED ( BI_Vacancy[GUID] )
    )
)

This code uses the CALCULATE function to compute the sum of the VacancyAddedCount column in the BI_Fact_Activity table, while applying two filter conditions. The first filter condition specifies that only rows in the BI_Vacancy table where the value of the eDate column is equal to DATE(2222,02,22) should be included in the calculation. The second filter condition specifies that only rows in the BI_Fact_Activity table where the value of the GUID column is equal to the value of the GUID column in the related row in the BI_Vacancy table should be included in the calculation.

This code is just an example and you may need to modify it to match the specific details of your data model and the filter context in your visual.

Best regards,

Johannes