Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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 😞
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |