Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Firstnonblank rows based on another column per group

Hi everyone,  I have a situation where I need to move the value of a row to the firstnonblank row based on another column per group.  In my example below, I would love to move the value of "iscom...
Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    Anonymous,

     

    Try these measures:

     

    iscomplained calc =
VAR vCurrentStatus =
    MAX ( Table2[status] )
VAR vValueBlankStatus =
    CALCULATE (
        MAX ( Table2[iscomplained] ),
        ALLSELECTED ( Table2 ),
        VALUES ( Table2[customerno] ),
        Table2[status] = BLANK ()
    )
VAR vMinStatusDate =
    CALCULATE (
        MIN ( Table2[status date] ),
        ALLSELECTED ( Table2 ),
        VALUES ( Table2[customerno] ),
        NOT Table2[status] = BLANK ()
    )
VAR vMinStatus =
    CALCULATE (
        MIN ( Table2[status] ),
        ALLSELECTED ( Table2 ),
        VALUES ( Table2[customerno] ),
        Table2[status date] = vMinStatusDate
    )
VAR vResult =
    SWITCH (
        TRUE (),
        ISBLANK ( vCurrentStatus ), BLANK (),
        IF (
            NOT ISBLANK ( vValueBlankStatus )
                && vCurrentStatus = vMinStatus,
            vValueBlankStatus,
            MAX ( Table2[iscomplained] )
        )
    )
RETURN
    vResult

iscomplained final = 
SUMX ( VALUES ( Table2[customerno] ), [iscomplained calc] )

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi again, 

 

thanks again for the quick reply. I have attached a table with a sample data.., and also desired outcome

The idea would be that, the value of iscomplained, moves to the first non blank status, based on the earliest date. So for customer AA, the first non blank status is on 04-02-2020

Current situation

customernostatusstatus dateiscomplained
AA 01.02.20201
AAreturned06.02.2020 
AAcanceled04.02.2020 
AAprocessing06.02.2020 
BCsent01.03.20201
CDsent01.06.2020 
GHDelivered01.07.20201

 

Desired outcome

customernostatusstatus dateiscomplained
AA 01.02.2020 
AAreturned06.02.2020 
AAcanceled04.02.20201
AAprocessing06.02.2020 
BCsent01.03.20201
CDsent01.06.2020 
GHDelivered01.07.20201

 

Appreciate it:)

DataInsights's avatar
DataInsights
Super User
5 years ago

Anonymous,

 

Try these measures:

 

iscomplained calc =
VAR vCurrentStatus =
    MAX ( Table2[status] )
VAR vValueBlankStatus =
    CALCULATE (
        MAX ( Table2[iscomplained] ),
        ALLSELECTED ( Table2 ),
        VALUES ( Table2[customerno] ),
        Table2[status] = BLANK ()
    )
VAR vMinStatusDate =
    CALCULATE (
        MIN ( Table2[status date] ),
        ALLSELECTED ( Table2 ),
        VALUES ( Table2[customerno] ),
        NOT Table2[status] = BLANK ()
    )
VAR vMinStatus =
    CALCULATE (
        MIN ( Table2[status] ),
        ALLSELECTED ( Table2 ),
        VALUES ( Table2[customerno] ),
        Table2[status date] = vMinStatusDate
    )
VAR vResult =
    SWITCH (
        TRUE (),
        ISBLANK ( vCurrentStatus ), BLANK (),
        IF (
            NOT ISBLANK ( vValueBlankStatus )
                && vCurrentStatus = vMinStatus,
            vValueBlankStatus,
            MAX ( Table2[iscomplained] )
        )
    )
RETURN
    vResult

iscomplained final = 
SUMX ( VALUES ( Table2[customerno] ), [iscomplained calc] )

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    This worked, Thanks alot 🙂