Forum Discussion

powerbiuser365's avatar
powerbiuser365
New Member
2 years ago
Solved

Need help with column

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

UserProductT/FNumber
A1SCFALSE1
A1SCFALSE3
A1SCTRUE4
A1CCTRUE5
A1MATRUE5
A2AFFALSE5
A2AFTRUE5
A3MAFALSE3
A3MATRUE3
A4AWFALSE3
A4AWTRUE5
A5SMFALSE4
A5SMTRUE2
A5SATRUE3

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

 

UserProductT/FNumberStatus
A1SCFALSE1 
A1SCFALSE3 
A1SCTRUE4Yes
A1CCTRUE5Neutral
A1MATRUE5Neutral
A2AFFALSE5 
A2AFTRUE5No
A3MAFALSE3 
A3MATRUE3No
A4AWFALSE3 
A4AWTRUE5Yes
A5SMFALSE4 
A5SMTRUE2No
A5SATRUE3Neutral

 

Thankyou so much in advance

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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