Count last occurrence, criteria in multiple rows

[govi]

Hi,

I have this table:

In column "newUser" I return a "1" when the column "status" says "SubscriptionCreated"

In column "excludedUser" I return a "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 included again, on 9-1-2019 the user is excluded for real, and then "1" should be returned.

Put simple: if "ExcludedFromProject" occurs more than once for a user, "1" must returned at the last occurence of "ExcludedFromProject".

Can someone help?

Thank you!

govi

[v-lionel-msft]

Hi @govi ,

Try the measures:

newUser = 
IF(
    MAX(Sheet1[status]) = "SC",
    1,
    BLANK()
)

excludedUser = 
VAR x = 
CALCULATE(
    MAX(Sheet1[timestamp]),
    FILTER(
        ALL(Sheet1),
        Sheet1[userId] = SELECTEDVALUE(Sheet1[userId]) && Sheet1[status] = "EFP"
    )
)
VAR y = 
CALCULATE(
    MIN(Sheet1[timestamp]),
        FILTER(
            ALL(Sheet1),
            Sheet1[userId] = SELECTEDVALUE(Sheet1[userId]) && Sheet1[status] = "EFP"
        )
)
VAR z = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(Sheet1[timestamp]) = x && SELECTEDVALUE(Sheet1[status]) = "EFP", "1",
    SELECTEDVALUE(Sheet1[timestamp]) >= y && SELECTEDVALUE(Sheet1[timestamp]) <= x && SELECTEDVALUE(Sheet1[status]) = "EFP", "no value",
    BLANK()
)
RETURN
z

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.

[govi]

Hi @v-lionel-msft ,

Thanks for your solution!

However I encounter a problem:

I would like to create a column with the "excludedUser" directly in my table.

I tried to put your DAX in a new column but that didn't work out.

Could you help me again?

Thanks!

Govi

[v-lionel-msft · Accepted solution]

Hi @govi ,

If you want calculated columns, you can do like this:

Column = 
IF(
    Sheet1[status] = "SC",
    1, BLANK()
)

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

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.

[govi]

Brilliant @v-lionel-msft !

Thanks you!