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

4 Replies

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

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

    Result First ID CC =
MINX ( FILTER ( Data, Data[Category] = EARLIER ( Data[Category] ) ), Data[ID] )

     

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        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] )