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 "iscomplained" where status is blank to firstnonblank status for customer "AA". It would be amazing if this could be done with a measure rather than a calculated column. I would still appreciate any solution though. 

 
Looking forward to hearing you all 🙂

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] )

     

     

6 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    Anonymous,

     

    Try this solution. The second measure is required in order to calculate totals. Make sure the status column contains blank where no status exists (not an empty string).

     

    Measures:

     

    iscomplained calc = 
VAR vCurrentStatus =
    MAX ( Table1[status] )
VAR vValueBlankStatus =
    CALCULATE ( MAX ( Table1[iscomplained] ), ISBLANK ( Table1[status] ) )
VAR vMinStatus =
    CALCULATE ( MIN ( Table1[status] ), ALLEXCEPT ( Table1, Table1[customerno] ) )
VAR vResult =
    SWITCH (
        TRUE (),
        ISBLANK ( vCurrentStatus ), BLANK (),
        IF (
            NOT ISBLANK ( vValueBlankStatus )
                && vCurrentStatus = vMinStatus,
            vValueBlankStatus,
            MAX ( Table1[iscomplained] )
        )
    )
RETURN
    vResult

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

     

    In the visual, set the status field to "Show items with no data":

     

     

     

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