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: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

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
228 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
179 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
180 Views
0

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All
Top Solution Authors
View All