Forum Discussion

MikeHunt's avatar
MikeHunt
Regular Visitor
2 years ago
Solved

Create a column which generates revision ID based on Status column

I have Code, Status, Created (date) columns in my Power BI data. I want to create a another column named Revision Number column that will filter per Code and group one revision with ID. All revisions starts Initiated and ends with Approved.

 

Example below.

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, MikeHunt 

    I use the data shown in your image as shown below:

    First, I created an Index column using PowerQuery:

    I created the Revision Number column using the following DAX expression:

    Revision Number =
RANKX (
    FILTER (
        'Table',
        'Table'[Status] = "Approved"
            && 'Table'[Code] = EARLIER ( 'Table'[Code] )
    ),
    'Table'[Index],
    ,
    ASC,
    SKIP
)

    Here are the results:

    I've provided the PBIX file for this time below, and it would be great if I could help you.

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, MikeHunt 

    I use the data shown in your image as shown below:

    First, I created an Index column using PowerQuery:

    I created the Revision Number column using the following DAX expression:

    Revision Number =
RANKX (
    FILTER (
        'Table',
        'Table'[Status] = "Approved"
            && 'Table'[Code] = EARLIER ( 'Table'[Code] )
    ),
    'Table'[Index],
    ,
    ASC,
    SKIP
)

    Here are the results:

    I've provided the PBIX file for this time below, and it would be great if I could help you.

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.