Forum Discussion
How to do a compare based on two columns
Thanks for helping! We have two columns of data- column 1 is a date, column 2 is a text field with a pending action . This along with a primary key of project number. We want to be able to choose dates, and have a visual list those dates that have the same pending actions. For example-
| Project | Source File Date | Pending_Activity |
| TD1005855 | 1/1/2023 | 08-All Requirements Met Not Completed |
| TD1005855 | 10/29/2023 | 08-All Requirements Met Not Completed |
| TD1005855 | 11/5/2023 | 08-All Requirements Met Not Completed |
| TD1005855 | 11/12/2023 | 08-All Requirements Met Not Completed |
| TD1005855 | 11/26/2023 | 08-All Requirements Met Not Completed |
| TD1013540 | 1/1/2023 | 01-Cancelled/Pending Cancellation |
| TD1013540 | 10/29/2023 | 02-Closed |
| TD1013540 | 11/5/2023 | 02-Closed |
| TD1013540 | 11/12/2023 | 02-Closed |
| TD1013540 | 11/26/2023 | 02-Closed |
| TD1018901 | 1/1/2023 | 13-PreConstruction Not Completed |
| TD1018901 | 10/29/2023 | 11-ATP for Construction Not Completed |
| TD1018901 | 11/5/2023 | 10-WO Sent to RPPM Not Completed |
| TD1018901 | 11/12/2023 | 15-Electrical Construction Not Started |
| TD1018901 | 11/26/2023 | 17-Electrical Construction Not Completed |
We want to be able to create a list of projects where the pending activity for the latest week- 11/26/2023- matches the pending activity on 1/1/2023. In the sample above, only TD1005855 would show in the visual. We have about 30,000 projects, and want a visual that would illustrate. We MAY also want to compare week to week, but for now lookingonly to compare current week to 1/1/2023. Any thoughts are appreciated.
Hi Anonymous
If you don’t have a Project dimension table:
In Power Query, create a new query by referencing your table. Remove all columns except [Project]. Remove duplicates. Close Power Query.
Create a 1:* relationship between ‘Project’[Project] and ‘Table’[Project] (single)3 measures:
_FirstStatus = IF( ISINSCOPE( 'Project'[Project] ), CALCULATE( MAX( 'StatusTable'[Pending_Activity] ), 'StatusTable'[Source File Date] = DATE( 2023, 1, 1 ) ) ) _LatestStatus = VAR _MaxDt = MAXX( ALL( 'StatusTable' ), [Source File Date] ) RETURN IF( ISINSCOPE( 'Project'[Project] ), CALCULATE( MAX( 'StatusTable'[Pending_Activity] ), 'StatusTable'[Source File Date] = _MaxDt ) ) Compare = COUNTROWS( FILTER( VALUES( 'Project'[Project] ), [_FirstStatus] = [_LatestStatus] ) )Let me know if you have questions.
Compare latest status to first status.pbix
2 Replies
Hi Anonymous
If you don’t have a Project dimension table:
In Power Query, create a new query by referencing your table. Remove all columns except [Project]. Remove duplicates. Close Power Query.
Create a 1:* relationship between ‘Project’[Project] and ‘Table’[Project] (single)3 measures:
_FirstStatus = IF( ISINSCOPE( 'Project'[Project] ), CALCULATE( MAX( 'StatusTable'[Pending_Activity] ), 'StatusTable'[Source File Date] = DATE( 2023, 1, 1 ) ) ) _LatestStatus = VAR _MaxDt = MAXX( ALL( 'StatusTable' ), [Source File Date] ) RETURN IF( ISINSCOPE( 'Project'[Project] ), CALCULATE( MAX( 'StatusTable'[Pending_Activity] ), 'StatusTable'[Source File Date] = _MaxDt ) ) Compare = COUNTROWS( FILTER( VALUES( 'Project'[Project] ), [_FirstStatus] = [_LatestStatus] ) )Let me know if you have questions.
Compare latest status to first status.pbix