Forum Discussion

Sarath5c8's avatar
Sarath5c8
Frequent Visitor
2 years ago

Need to prioritize and get the reference based column

Have Parent - CHild relationship 

where 1Parent with multiple children and their status are provided we need to derive Parent status based on the prefrence of Green-1st, Amber - 2nd and Red-3rd.

So if any Child under that Parent has Green, By default that Parent status will Green etc.

Below is the table data accordingly We Parent Status need to be found.

ParentChildChild StatusParent Status - Expecte output
P1C1RedGreen
P1C2AmberGreen
P1C3GreenGreen
P2C4AmberAmber
P2C5AmberAmber
P2C6AmberAmber
P2C7RedAmber
P3C8RedRed
P3C9RedRed
P3C10RedRed
P3C11RedRed

 

Need Help
Show and Tell

1 Reply

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    Hi , 

    Please try the following calc column:

     

    Status =
VAR __GREEN =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Parent] = EARLIER ( 'Table'[Parent] )
                && 'Table'[Child Status] = "Green"
        )
    )
VAR __AMBER =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Parent] = EARLIER ( 'Table'[Parent] )
                && 'Table'[Child Status] = "Amber"
        )
    )
VAR __RED =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Parent] = EARLIER ( 'Table'[Parent] )
                && 'Table'[Child Status] = "Red"
        )
    )
RETURN
    SWITCH (
        TRUE (),
        NOT ( ISBLANK ( __GREEN ) ), "Green",
        NOT ( ISBLANK ( __AMBER ) ), "Amber",
        NOT ( ISBLANK ( __RED ) ), "Red"
    )