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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
gazzo1967
Helper III
Helper III

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

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_IDUserNameLocationBA_ Roleemailansweractivity_date
1Mike.userTown BA Wearerblah1Required Standard Achieved01/01/2023
2Dave.userCityBA Wearerblah2Required Standard Achieved03/02/2023
2Dave.userCityBA Wearerblah2Not Assessed04/03/2023
2Dave.userCityBA Wearerblah2Required Standard Achieved21/06/2021
3Joe.userCityBA Wearerblah3Required Standard Achieved27/12/2021
6Andy.userTown BA Wearerblah4Required Standard Achieved12/08/2021
3Joe.userCityBA Wearerblah2Required Standard Achieved20/07/2021
8Paul.userTown BA Wearerblah5Required Standard Achieved16/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_IDUserNameLocationBA RoleemailGAZTESTBA1
1Mike.userTown BA Wearerblah11
2Dave.userCityBA Wearerblah21
3Joe.userCityBA Wearerblah32
6Andy.userTown BA Wearerblah4null/0
8Paul.userTown BA Wearerblah51



Any Help Appreciated
Gary

1 ACCEPTED SOLUTION

10 REPLIES 10
aditya0125
Resolver I
Resolver I

Please Provide valid sample data.

@aditya0125 
I have edited the original post!

 

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

@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_0-1679344510967.png

 

@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 🙂

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

@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
Super User
Super User

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.

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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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