Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
danjswade
Frequent Visitor

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

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.
1 ACCEPTED SOLUTION
danjswade
Frequent Visitor

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)
            )
)

View solution in original post

2 REPLIES 2
danjswade
Frequent Visitor

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
Resolver II
Resolver II

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors