Need help with column

[powerbiuser365]

Hi All,

I wanted to see a user has changed the status or not and have a new column Status for True Values.

Sample Data

User	Product	T/F	Number
A1	SC	FALSE	1
A1	SC	FALSE	3
A1	SC	TRUE	4
A1	CC	TRUE	5
A1	MA	TRUE	5
A2	AF	FALSE	5
A2	AF	TRUE	5
A3	MA	FALSE	3
A3	MA	TRUE	3
A4	AW	FALSE	3
A4	AW	TRUE	5
A5	SM	FALSE	4
A5	SM	TRUE	2
A5	SA	TRUE	3

Basically we are comparing True vs False when a user has more than one row for same product based on number.

Ex:

A1 user CC has only one row with TRUE value so the status is neutral and for product SC it has both TRUE and FALSE so now we need to check max number for FALSE values and see if the max number is greater than TRUE number and if the number is greater then it should say Status is "Yes".

If the number is small or equal to max False value then it should say Status "No" like user A5 for product SM number changed but TRUE value number lower so it should say status not changed also user A3 has both TRUE and FALSE but the number is same so also the Status is "No".

Sorry if I am confusing please see the o/p below for more clarification

User	Product	T/F	Number	Status
A1	SC	FALSE	1
A1	SC	FALSE	3
A1	SC	TRUE	4	Yes
A1	CC	TRUE	5	Neutral
A1	MA	TRUE	5	Neutral
A2	AF	FALSE	5
A2	AF	TRUE	5	No
A3	MA	FALSE	3
A3	MA	TRUE	3	No
A4	AW	FALSE	3
A4	AW	TRUE	5	Yes
A5	SM	FALSE	4
A5	SM	TRUE	2	No
A5	SA	TRUE	3	Neutral

Thankyou so much in advance

[Anonymous · Accepted solution]

Hi @powerbiuser365 ,

You can create a calculated column as below to get it, please find the details in the attachment.

Status = 
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[Product] ),
        FILTER (
            'Table',
            'Table'[User] = EARLIER ( 'Table'[User] )
                && 'Table'[Product] = EARLIER ( 'Table'[Product] )
        )
    )
VAR _maxfalse =
    CALCULATE (
        MAX ( 'Table'[Number] ),
        FILTER (
            'Table',
            'Table'[User] = EARLIER ( 'Table'[User] )
                && 'Table'[Product] = EARLIER ( 'Table'[Product] )
                && 'Table'[T/F] = FALSE ()
        )
    )
RETURN
    IF (
        'Table'[T/F] = TRUE (),
        IF ( _count = 1, "Neutral", IF ( 'Table'[Number] <= _maxfalse, "No", "Yes" ) ),
        BLANK ()
    )

Best Regards