cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Create a column that shows 1 or 0 whether an 'End Date' column is the maximum 'End Date'.

Hi All,

 

This might be pretty simple, but I am really struggling to achieve this:

 

I have a TestCase Detail dataset in Power BI having the below columns. There are other columns also, which I have not shown.

Capture.PNG

There are a number of Test cases(TestCaseId column) which are run for a number of Projects(Projects column). Projects might have a particular TestCase run many times. I need to find that record(that particular run) which is having the latest EndDate for that TestCase and for that Project. So I have created a column Flag , which will be 0 or 1 based on whether that record is having the lastest EndDate or not. And accordingly, (whichever record is having Flag as 1, I need to pick up the Status for that record)

 

Flag = IF(TestDetail_db[EndDate] =CALCULATE(MAX(TestDetail_db[EndDate]),ALLEXCEPT(TestDetail_db,TestDetail_db[TestCaseId])),1,0)

 

 
 

Now the problem is : In the visualization, I have a project slicer, which probably is not getting cleared even even I am selecting all (probably because of ALLEXCEPT function), and is not showing me the result properly.

 

Example - For Project A, if there are 10 records,(out of which 1 test case repeated 3 times, i.e 3 runs), then there should be ideally 8 records with value in Flag , and 2 records with value 0.  And I should be knowing that I need to pick up the statuses of those having Flag as 1.

Now if a same TestCaseId (that was also present in Project A), is also present in Project B, it looks like my Flag is not giving what I am looking for.

 

Any help is really appreciated! Thanks in advance.

Capture1.PNG

Regards,

Ayushi

2 REPLIES 2
Highlighted
Super User V
Super User V

Re: Create a column that shows 1 or 0 whether an 'End Date' column is the maximum 'End Date'.

Hi @aagrawal124 ,

 

Very difficult to understanf from your data.

 

Try  a measure like this .

 

Try troubleshooting . Uncomment the IF part and see if value of a is returning the MAX Date per Test cAse ID.

 

Flag =
VAR a =
    CALCULATE (
        MAX ( TestDetail_db[EndDate] ),
        ALLEXCEPT (
            TestDetail_db,
            TestDetail_db[TestCaseId]
        )
    )
RETURN
    IF (
        MAX ( TestDetail_db[EndDate] ) = a,
        1,
        0
    )

 

 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 

Highlighted
Community Support
Community Support

Re: Create a column that shows 1 or 0 whether an 'End Date' column is the maximum 'End Date'.

Hi @aagrawal124 ,

 

Is it convenient to share your .pbix file? Or give a data table which is easy to copy.  I want to test your  problem.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors