Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have an excel spreadsheet (which I provided a little example of below) where it keeps track of all of the audits that we have performed since 2016. This excel spreadsheet shows the business name, project status, project type, report rating, and more. Since this file goes back all the way to 2016, there are multiple times a business can be within the spreadsheet. I was able to come up with a formula to show the most recent year we have audited each business. But now i want to show the most recent audit report rating.
Here is the meaure formula I created to show the most recent audit done (year) for each business:
Year | Business Unit | Project Type | Report Issue Date | Report Rating | Status |
2016 | Pop | Financial | Complete | ||
2016 | Paper | Financial | Complete | ||
2016 | Paper | IT | Complete | ||
2016 | Pop | IT | Complete | ||
2016 | Pen | Full Scope | Complete | ||
2016 | Juice | Full Scope | Complete | ||
2017 | Pop | Financial | Complete | ||
2017 | Paper | Financial | Complete | ||
2017 | Whiskey | Full Scope | Complete | ||
2017 | Paper | Full Scope | Complete | ||
2017 | Wine | Full Scope | Complete | ||
2018 | Blanket | Full Scope | 4/11/2018 | Satisfactory | Complete |
2018 | Beer | Full Scope | 5/15/2018 | Satisfactory | Complete |
2018 | Juice | Full Scope | 6/21/2018 | Satisfactory | Complete |
2018 | Pop | Full Scope | 8/9/2018 | Satisfactory | Complete |
2018 | Printer | Investigation | 5/21/2018 | N/A | Complete |
2018 | Water | Full Scope | Postponed | ||
2018 | Wine | Management Request | Postponed | ||
2018 | Whiskey | Financial | Improvement Needed | Complete | |
2018 | Pop | Financial | Complete | ||
2018 | Paper | Financial | Complete | ||
2019 | Whiskey | Full Scope | 6/23/2019 | Improvement Needed | Complete |
2019 | Pencil | Full Scope | 4/26/2019 | Less Than Satisfactory | Complete |
2019 | Printer | Full Scope | 2/11/2020 | Improvement Needed | Complete |
2019 | Water | Full Scope | 11/7/2019 | Satisfactory | Complete |
2019 | Wine | Special Project | Postponed | ||
2019 | Pen | Full Scope | 11/22/2019 | Satisfactory | Complete |
2019 | Beer | Management Request | 9/10/2019 | Satisfactory | Complete |
2019 | Paper | Financial | N/A | Complete | |
2019 | Pop | Financial | N/A | Complete | |
2019 | Pencil | Management Request | N/A | Complete | |
2019 | Pop | Investigation | N/A | Complete | |
2020 | Pencil | Management Request | N/A | Fieldwork | |
2020 | Paper | Full Scope | Planning | ||
2020 | Wine | Full Scope | Postponed | ||
2020 | Pencil | Full Scope | Not Started | ||
2020 | Juice | Full Scope | Not Started | ||
2020 | Paper | Financial | Not Started | ||
2020 | Pop | Financial | Not Started | ||
2020 | Paper | Special Project | Not Started | ||
2020 | Pen | Special Project | Not Started | ||
2020 | Wine | Special Project | Not Started |
Solved! Go to Solution.
Hi @Anonymous ,
Add the condition to the if statement.
Measure =
VAR max_report_date =
CALCULATE (
MAX ( 'Table'[Report Issue Date] ),
ALLEXCEPT ( 'Table', 'Table'[Business Unit] )
)
RETURN
IF (
NOT ( ISBLANK ( MAX ( 'Table'[Report Issue Date] ) ) )
&& MAX ( 'Table'[Report Issue Date] ) = max_report_date
&& MAX ( 'Table'[Project Type] ) = "Full Scope",
1,
0
)
You can also refer to the Sample pbix
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
On the Data that you have shared, please show the exact expected result.
Below is what the solution should look like. It would show the Report Rating of the most recent Full Scope (Project Type) audit. If a businuss unit does not have a Report Rating of a full scope auduit it should be left blank.
Year | Business Unit | Project Type | Report Issue Date | Report Rating | Status |
2018 | Beer | Full Scope | 5/15/2018 | Satisfactory | Complete |
2018 | Blanket | Full Scope | 4/11/2018 | Satisfactory | Complete |
2018 | Juice | Full Scope | 6/21/2018 | Satisfactory | Complete |
2019 | Pen | Full Scope | 11/22/2019 | Satisfactory | Complete |
2019 | Pencil | Full Scope | 4/26/2019 | Less Than Satisfactory | Complete |
2018 | Pop | Full Scope | 8/9/2018 | Satisfactory | Complete |
2019 | Printer | Full Scope | 2/11/2020 | Improvement Needed | Complete |
2019 | Water | Full Scope | 11/7/2019 | Satisfactory | Complete |
2019 | Whiskey | Full Scope | 6/23/2019 | Improvement Needed | Complete |
Hi,
If i filter the Project type column on Full Scope and the Report issue data on non blanks, i am left with your exact expected result without writing any formula at all. This is because after applying these 2 filter conditions, only one row combination of Year and Business Unit is left. So why do we need to write any formula at all. Just apply filters in the visual as i have mentioend. Here's a screenshot
Hi @Anonymous ,
Please try the DAX below:
Measure =
//Get the latest report date for each Business Unit
VAR max_report_date =
CALCULATE (
MAX ( 'Table'[Report Issue Date] ),
ALLEXCEPT ( 'Table', 'Table'[Business Unit] )
)
//Determine whether the date of the current line is the latest date
RETURN
IF (
NOT ( ISBLANK ( MAX ( 'Table'[Report Issue Date] ) ) )
&& MAX ( 'Table'[Report Issue Date] ) = max_report_date,
1,
0
)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This did not give me what i was looking for. Below is what the solution should look like. It would show the Report Rating of the most recent Full Scope (Project Type) audit. If a businuss unit does not have a Report Rating of a full scope auduit it should be left blank.
Year | Business Unit | Project Type | Report Issue Date | Report Rating | Status |
2018 | Beer | Full Scope | 5/15/2018 | Satisfactory | Complete |
2018 | Blanket | Full Scope | 4/11/2018 | Satisfactory | Complete |
2018 | Juice | Full Scope | 6/21/2018 | Satisfactory | Complete |
2019 | Pen | Full Scope | 11/22/2019 | Satisfactory | Complete |
2019 | Pencil | Full Scope | 4/26/2019 | Less Than Satisfactory | Complete |
2018 | Pop | Full Scope | 8/9/2018 | Satisfactory | Complete |
2019 | Printer | Full Scope | 2/11/2020 | Improvement Needed | Complete |
2019 | Water | Full Scope | 11/7/2019 | Satisfactory | Complete |
2019 | Whiskey | Full Scope | 6/23/2019 | Improvement Needed | Complete |
Hi @Anonymous ,
Add the condition to the if statement.
Measure =
VAR max_report_date =
CALCULATE (
MAX ( 'Table'[Report Issue Date] ),
ALLEXCEPT ( 'Table', 'Table'[Business Unit] )
)
RETURN
IF (
NOT ( ISBLANK ( MAX ( 'Table'[Report Issue Date] ) ) )
&& MAX ( 'Table'[Report Issue Date] ) = max_report_date
&& MAX ( 'Table'[Project Type] ) = "Full Scope",
1,
0
)
You can also refer to the Sample pbix
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.