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

[Daniel29195]

@danjswade 

not sure if i understand what you want to achieve,but 

assuming bi_vacancy is linked to bi_fact_activity with a 1-many relationship going towards the bi_fact_activity, 

calculate(

            sum(BI_Fact_Activity[VacancyAddedCount]),

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

this should work i guess. 

if not, please share more info about the issue . 

best regards..

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[danjswade]

Thanks for the reply.

Unfortunately, that didn't work. Simply returned a blank

What further information would you need?

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result very clearly.

[v-shex-msft]

HI @danjswade,

You can try to use following measure if it suitable for your requirement:

formula =
CALCULATE (
    SUM ( BI_Fact_Activity[VacancyAddedCount] ),
    FILTER ( ALLSELECTED ( BI_Vacancy ), BI_Vacancy[eDate] = DATE ( 2222, 02, 22 ) )
)

Regards,

Xiaoxin Sheng