Help Combining 2 CALCULATE functions into 1 (I tried :*( ) EDITED

[gazzo1967]

Hi All
Hope you are all well 🙂
I have 2 Measures
I would like to combine them so that instead of 

BA_Count_Total= CALCULATE(COUNTROWS('reporting performance'),SEARCH("BA Wearer",'reporting performance'[BA Role],,0)&& SEARCH("Required Standard Achieved",'reporting performance'[answer],,0))

then i use this to calculate count in time period

BA_Last12Months = CALCULATE ([BA_Count_Total],DATESINPERIOD ('reporting performance'[activity_date],MAX('reporting performance'[activity_date]),-12,MONTH))

I would like to do something like this (this doesnt work although no Syntax error)

GAZTESTBA2 = CALCULATE(COUNTROWS('reporting performance'),SEARCH("BA Wearer",'reporting performance'[Role_BA],,0)&& SEARCH("Required Standard Achieved",'reporting performance'[answer],,0),
DATESINPERIOD ('reporting performance'[activity_date],MAX('reporting performance'[activity_date]),-12,MONTH)) but it needs to be against each user.

reporting performance

Role_ID	UserName	Location	BA_ Role	email	answer	activity_date
1	Mike.user	Town	BA Wearer	blah1	Required Standard Achieved	01/01/2023
2	Dave.user	City	BA Wearer	blah2	Required Standard Achieved	03/02/2023
2	Dave.user	City	BA Wearer	blah2	Not Assessed	04/03/2023
2	Dave.user	City	BA Wearer	blah2	Required Standard Achieved	21/06/2021
3	Joe.user	City	BA Wearer	blah3	Required Standard Achieved	27/12/2021
6	Andy.user	Town	BA Wearer	blah4	Required Standard Achieved	12/08/2021
3	Joe.user	City	BA Wearer	blah2	Required Standard Achieved	20/07/2021
8	Paul.user	Town	BA Wearer	blah5	Required Standard Achieved	16/03/2023

MODEL RELATIONSHIP employee data table  linked to reporting performance by email (not Shown)


OUTCOME count in a 12 month period based on TODAY() and TODAY()-12 (BA_Last12Months)

Role_ID	UserName	Location	BA Role	email	GAZTESTBA1
1	Mike.user	Town	BA Wearer	blah1	1
2	Dave.user	City	BA Wearer	blah2	1
3	Joe.user	City	BA Wearer	blah3	2
6	Andy.user	Town	BA Wearer	blah4	null/0
8	Paul.user	Town	BA Wearer	blah5	1

Any Help Appreciated
Gary

[aditya0125]

Please Provide valid sample data.

[gazzo1967]

@aditya0125 
I have edited the original post!

[lbendlin]

Still not clear. Please describe the business question you are trying to answer.

[gazzo1967]

@lbendlin 
Thanks for your patience on this 🙂

Essentially there are 2 calculations
The first one to create the count
the second to supply basis of the count within a 12 month dynamic period between Today() and Today() -12
I was just wondering if there was a way to combine the 2 calculations into one to streamline the process and reduce the amount of Measures
Regards gary

[lbendlin · Accepted solution]

[gazzo1967]

@lbendlin 
Just a question to  broaden my knowledge.
I can see that in order for this to work on a table and be filtered with a slicer I would have to include the 'reporting performance' [Role_ID].
And i have also tested using other fields.

But how come it wont work with an OR (||) in the ALLEXCEPT line?
regards Gary 🙂

[lbendlin]

filter context changes are processed on top of each other. To avoid that you would need to use KEEPFILTERS

[gazzo1967]

@lbendlin 
Thank you 🙂

It worked perfectly and you have given me some more functions to read up about
very much appreciate you taking time out to look at this 🙂

[lbendlin]

Your sample data doesn't match your issue description. Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[gazzo1967]

@lbendlin 
Apologies I have changed it.
I copied and pasted the wrong excel sample I had created 😞