Forum Discussion

IghorLima26's avatar
IghorLima26
Frequent Visitor
2 years ago
Solved

Categorize based on rows with the same values ​​and in sequence

Hi,  I would like to create a column (RESULT) with the condition that if the category is repeated sequentially (ID) it returns the first ID.  For example:  
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Thank you for your clarification.

    Please check the below picture and the attached pbix file.

     

     

     

    Result First ID CC =
VAR _currentid = Data[ID]
VAR _condition =
    ADDCOLUMNS (
        Data,
        "@condition",
            VAR _currentid = Data[ID]
            VAR _currentrow = Data[Category]
            VAR _prevrow =
                MAXX (
                    OFFSET ( -1, Data, ORDERBY ( Data[ID], ASC ),,, MATCHBY ( Data[ID] ) ),
                    Data[Category]
                )
            RETURN
                IF ( _currentrow = _prevrow || BLANK (), 0, 1 )
    )
VAR _partition =
    ADDCOLUMNS (
        _condition,
        "@partition", SUMX ( FILTER ( _condition, Data[ID] <= EARLIER ( Data[ID] ) ), [@condition] )
    )
VAR _currentpartition =
    MINX ( FILTER ( _partition, Data[ID] = _currentid ), [@partition] )
RETURN
    MINX ( FILTER ( _partition, [@partition] = _currentpartition ), Data[ID] )