Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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)
Role_ID | UserName | Location | BA_ Role | 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 |
Role_ID | UserName | Location | BA Role | 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
Solved! Go to Solution.
Please Provide valid sample data.
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
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 🙂
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 😞
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
83 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
129 | |
108 | |
63 | |
55 |