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

[Anonymous]

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.

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 1 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.

Regards,

Ayushi

[v-lionel-msft]

Hi @Anonymous ,

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.

[harshnathani]

Hi @Anonymous ,

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)