• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power BI: Dax: New column: If criteria occurs more than once return "1" with the last occurence

govi

Member
Hi all,
I have this table:
67487
I want to create a column "excludedUser" in which to return "1" when the column "status" says "ExcludedFromProject"

But sometimes a user is accidentally excluded, and later included ("IncludedInProject") again.

In the example above (rows in yellow) the first "ExcludedFromProject" for user 14195 must not return "1" in column "excludedUser" because is later the user is included again.

On 9-1-2019 the user is excluded for real, and then "1" should be returned.

Could someone help me?

Thanks!
govi
 

Attachments

  • 2020-04-29_130913.png
    2020-04-29_130913.png
    42.9 KB · Views: 0
I already have a solution:
Code:
Column 2 =
VAR x =
CALCULATE(
    MAX(Sheet1[timestamp]),
    FILTER(
        ALL(Sheet1),
        Sheet1[userId] = EARLIER(Sheet1[userId]) && Sheet1[status] = "EFP"
    )
)
VAR y =
CALCULATE(
    MIN(Sheet1[timestamp]),
        FILTER(
            ALL(Sheet1),
            Sheet1[userId] = EARLIER(Sheet1[userId]) && Sheet1[status] = "EFP"
        )
)
VAR z =
SWITCH(
    TRUE(),
    Sheet1[timestamp] = x && Sheet1[status] = "EFP", "1",
    Sheet1[timestamp] >= y && Sheet1[timestamp] <= x &&Sheet1[status] = "EFP", "no value",
    BLANK()
)
RETURN
z
z
 
Back
Top