Return "1" based on multiple criteria in other records

[govi]

Hi all,

I have a problem.

This is the table (events) I have:

Every time when a user on a specific date and the same "timing" ("morning" or "evening") has the status "Alert" and there are on this specific date for the same user records with status "AlertScore" it has to return "1" in "NewColumn". In all other records "0"has to be returned. So when there is "AlertScore" with no "Alert" it has to rerturn "0" as well.

I cannot figure this out, anyone of you can?

Would be great,

govi

[v-yuta-msft · Accepted solution]

@govi,

Create a calculate column using DAX below:

New Column = 
IF (
    Table1[Status] = "Alert",
    0,
    IF (
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                ALLEXCEPT ( Table1, Table1[timestamp], Table1[Timing], Table1[User] ),
                Table1[Status] = "Alert"
            )
        )
            <> 0,
        1,
        0
    )
)

 

Community Support Team _ Jimmy Tao

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

[govi]

@v-yuta-msft

Thanks for you help.

I tailored your solution a bit and it works perfect now

[aswinrhs]

You can create a calculated column to solve your problem. Refer the below link for detailed explanation. Hope it helps!

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns

[govi]

@aswinrhs

Thanks for your help