The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 😞
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
80 | |
77 | |
46 | |
39 |
User | Count |
---|---|
141 | |
110 | |
64 | |
64 | |
53 |