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, thanks for your reply. 

How would I adapt it, in a situation where we have a "status date"? 

truly appreciate your help 🙂

DataInsights's avatar
DataInsights
Super User
5 years ago

Anonymous,

 

Would you be able to provide sample data that I can copy to Power BI, as well as the expected result?

  • 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 🙂