Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
zhengfu123
Frequent Visitor

show rows with submission, while show recent date without submission date

‎05-30-2022 03:34 AM

Hi all, I am some question whether is it possible to do it in DAX or power quesry to clean such data, as the system do capture multiple inputs from user input, the sample data as below: 

Contract NumberDocumentNameEvaluationStartDateEvaluationEndDateDate Sent to EvaluatorEvaluator Submission DateEvaluator NameEvaluator Scorecard Status
Contract AKPI 14/22/2022 0:007/22/2022 0:004/20/2022 12:05 Evaluator AInvited
Contract AKPI 14/22/2022 0:007/22/2022 0:005/19/2022 8:12 Evaluator AInvited
Contract AKPI 14/22/2022 0:007/22/2022 0:005/11/2022 8:254/22/2022 6:55Evaluator ASubmitted
Contract AKPI 14/22/2022 0:007/22/2022 0:00  Evaluator BInvited
Contract AKPI 14/22/2022 0:007/22/2022 0:005/19/2022 8:12 Evaluator BInvited
Contract AKPI 14/22/2022 0:007/22/2022 0:004/20/2022 12:05 Evaluator CInvited
Contract AKPI 14/22/2022 0:007/22/2022 0:004/20/2022 12:05 Evaluator DInvited
Contract AKPI 14/22/2022 0:007/22/2022 0:005/19/2022 8:124/28/2022 7:25Evaluator DSubmitted
Contract AKPI 14/22/2022 0:007/22/2022 0:005/11/2022 8:254/28/2022 7:50Evaluator DSubmitted
Contract AKPI 23/20/2022 0:006/20/2022 0:005/11/2022 8:254/22/2022 6:32Evaluator ASubmitted
Contract AKPI 24/22/2022 0:007/22/2022 0:004/20/2022 12:05 Evaluator AInvited
Contract AKPI 24/22/2022 0:007/22/2022 0:004/22/2022 3:044/22/2022 6:31Evaluator ASubmitted
Contract AKPI 23/20/2022 0:006/20/2022 0:005/11/2022 8:25 Evaluator CInvited
Contract AKPI 24/22/2022 0:007/22/2022 0:004/20/2022 12:05 Evaluator CInvited
Contract AKPI 24/22/2022 0:007/22/2022 0:004/22/2022 3:04 Evaluator CInvited
Contract AKPI 23/20/2022 0:006/20/2022 0:005/11/2022 8:254/28/2022 7:47Evaluator DSubmitted
Contract AKPI 24/22/2022 0:007/22/2022 0:004/20/2022 12:05 Evaluator DInvited
Contract AKPI 24/22/2022 0:007/22/2022 0:004/22/2022 3:044/28/2022 1:51Evaluator DSubmitted
Contract AKPI 33/20/2022 0:006/20/2022 0:005/11/2022 8:254/22/2022 6:58Evaluator ASubmitted
Contract AKPI 34/22/2022 0:007/22/2022 0:004/20/2022 12:03 Evaluator AInvited
Contract AKPI 34/22/2022 0:007/22/2022 0:004/22/2022 3:044/22/2022 6:16Evaluator ASubmitted
Contract AKPI 34/22/2022 0:007/22/2022 0:00  Evaluator BInvited
Contract AKPI 33/20/2022 0:006/20/2022 0:004/21/2022 0:05 Evaluator CInvited
Contract AKPI 34/22/2022 0:007/22/2022 0:004/22/2022 3:04 Evaluator CInvited
Contract AKPI 33/20/2022 0:006/20/2022 0:005/11/2022 8:254/28/2022 8:03Evaluator DSubmitted
Contract AKPI 34/22/2022 0:007/22/2022 0:004/20/2022 12:03 Evaluator DInvited
Contract AKPI 34/22/2022 0:007/22/2022 0:004/22/2022 3:044/28/2022 0:47Evaluator DSubmitted
Contract AKPI 43/20/2022 0:006/20/2022 0:005/11/2022 8:254/22/2022 6:42Evaluator ASubmitted
Contract AKPI 44/22/2022 0:007/22/2022 0:004/20/2022 12:01 Evaluator AInvited
Contract AKPI 44/22/2022 0:007/22/2022 0:004/22/2022 3:04 Evaluator AInvited
Contract AKPI 43/20/2022 0:006/20/2022 0:005/11/2022 8:25 Evaluator CInvited
Contract AKPI 44/22/2022 0:007/22/2022 0:004/20/2022 12:01 Evaluator CInvited
Contract AKPI 44/22/2022 0:007/22/2022 0:004/22/2022 3:04 Evaluator CInvited
Contract AKPI 43/20/2022 0:006/20/2022 0:005/11/2022 8:254/28/2022 8:21Evaluator DSubmitted
Contract AKPI 44/22/2022 0:007/22/2022 0:004/20/2022 12:01 Evaluator DInvited
Contract AKPI 44/22/2022 0:007/22/2022 0:004/22/2022 3:044/28/2022 6:16Evaluator DSubmitted

 

So i end goal would be showing only the most recent "Date Sent To Evaluator" row if there is no "Evaluator Submission Date", is there is, then show the row with the most recent "Evaluator Submission Date" row only, there is possibility of two submission date, as well as "Date Sent To Evaluator". my Desired out put are as below:

Contract NumberDocumentNameEvaluationStartDateEvaluationEndDateDate Sent to EvaluatorEvaluator Submission DateEvaluator NameEvaluator Scorecard Status
Contract AKPI 14/22/2022 0:007/22/2022 0:005/11/2022 8:254/22/2022 6:55Evaluator ASubmitted
Contract AKPI 14/22/2022 0:007/22/2022 0:005/19/2022 8:12 Evaluator BInvited
Contract AKPI 14/22/2022 0:007/22/2022 0:004/20/2022 12:05 Evaluator CInvited
Contract AKPI 14/22/2022 0:007/22/2022 0:005/11/2022 8:254/28/2022 7:50Evaluator DSubmitted
Contract AKPI 23/20/2022 0:006/20/2022 0:005/11/2022 8:254/22/2022 6:32Evaluator ASubmitted
Contract AKPI 23/20/2022 0:006/20/2022 0:005/11/2022 8:25 Evaluator CInvited
Contract AKPI 23/20/2022 0:006/20/2022 0:005/11/2022 8:254/28/2022 7:47Evaluator DSubmitted
Contract AKPI 33/20/2022 0:006/20/2022 0:005/11/2022 8:254/22/2022 6:58Evaluator ASubmitted
Contract AKPI 34/22/2022 0:007/22/2022 0:00  Evaluator BInvited
Contract AKPI 34/22/2022 0:007/22/2022 0:004/22/2022 3:04 Evaluator CInvited
Contract AKPI 33/20/2022 0:006/20/2022 0:005/11/2022 8:254/28/2022 8:03Evaluator DSubmitted
Contract AKPI 43/20/2022 0:006/20/2022 0:005/11/2022 8:254/22/2022 6:42Evaluator ASubmitted
Contract AKPI 43/20/2022 0:006/20/2022 0:005/11/2022 8:25 Evaluator CInvited
Contract AKPI 43/20/2022 0:006/20/2022 0:005/11/2022 8:254/28/2022 8:21Evaluator DSubmitted

 

Hope I can get help from the SuperUsers, thanks!

Labels:
Message 1 of 2
272 Views
0
1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

‎06-01-2022 11:03 PM

Hi  @zhengfu123 ,

Base on the table ,create the below column:

Evaluator Submission Date2 = CALCULATE(MAX('Table'[Evaluator Submission Date]),FILTER(ALL('Table'),'Table'[Contract Number]=EARLIER('Table'[Contract Number])&&'Table'[DocumentName]=EARLIER('Table'[DocumentName])&&'Table'[Evaluator Name]=EARLIER('Table'[Evaluator Name])))
Date Sent to Evaluator2 = CALCULATE(MAX('Table'[Date Sent to Evaluator]),FILTER(ALL('Table'),'Table'[Contract Number]=EARLIER('Table'[Contract Number])&&'Table'[DocumentName]=EARLIER('Table'[DocumentName])&&'Table'[Evaluator Name]=EARLIER('Table'[Evaluator Name])))

Then create new table:

Table 2 = 
UNION (
    CALCULATETABLE (
        'Table',
        FILTER (
            ALL ( 'Table' ),
            'Table'[Evaluator Submission Date] = 'Table'[Evaluator Submission Date2]
                && 'Table'[Evaluator Submission Date2] <> BLANK ()
        )
    ),
    CALCULATETABLE (
        'Table',
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date Sent to Evaluator] = 'Table'[Date Sent to Evaluator2]
                && 'Table'[Evaluator Submission Date2] = BLANK ()
        )
    )
)

Output result is you expect:

vluwangmsft_0-1654149700918.pngvluwangmsft_1-1654149706857.png

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

View solution in original post

show rows with submission, while show recent date without submission date.pbix
Message 2 of 2
223 Views
0
1 REPLY 1
v-luwang-msft
Community Support
Community Support

‎06-01-2022 11:03 PM

Hi  @zhengfu123 ,

Base on the table ,create the below column:

Evaluator Submission Date2 = CALCULATE(MAX('Table'[Evaluator Submission Date]),FILTER(ALL('Table'),'Table'[Contract Number]=EARLIER('Table'[Contract Number])&&'Table'[DocumentName]=EARLIER('Table'[DocumentName])&&'Table'[Evaluator Name]=EARLIER('Table'[Evaluator Name])))
Date Sent to Evaluator2 = CALCULATE(MAX('Table'[Date Sent to Evaluator]),FILTER(ALL('Table'),'Table'[Contract Number]=EARLIER('Table'[Contract Number])&&'Table'[DocumentName]=EARLIER('Table'[DocumentName])&&'Table'[Evaluator Name]=EARLIER('Table'[Evaluator Name])))

Then create new table:

Table 2 = 
UNION (
    CALCULATETABLE (
        'Table',
        FILTER (
            ALL ( 'Table' ),
            'Table'[Evaluator Submission Date] = 'Table'[Evaluator Submission Date2]
                && 'Table'[Evaluator Submission Date2] <> BLANK ()
        )
    ),
    CALCULATETABLE (
        'Table',
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date Sent to Evaluator] = 'Table'[Date Sent to Evaluator2]
                && 'Table'[Evaluator Submission Date2] = BLANK ()
        )
    )
)

Output result is you expect:

vluwangmsft_0-1654149700918.pngvluwangmsft_1-1654149706857.png

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

show rows with submission, while show recent date without submission date.pbix
Message 2 of 2
224 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All