Forum Discussion

unknown917's avatar
unknown917
Helper IV
6 months ago
Solved

Sequential Order column based on multiple criteria

I am in need of creating a column "sequential order"  that will assign numerical order to each unique subset under the hub.  Here's the catch...the hub parent should always be first & not all hubs have subsets.  Each subset in the hub gets numbered sequentially.  Below is the desired result column "Seq Order".  Any help will be greatly appreciated.

 

HubHub NameSubset IDSubset NameSeq Order
1FW FW0
2INA702INA0
2INA624GUAVA1
3SB SB0
4LLLE700LLLE0
4LLLE203APPLE1
4LLLE521BANANA2
4LLLE700GRAPE3
4LLLE136ORANGE4
4LLLE953PEAR5
4LLLE630MANGO6
4LLLE631PINEAPPLE7
4LLLE632COCONUT8
5COL028COL0
6CIN609CIN0
7DTN DTN0
8MAR MAR0
9TWI TWI0
10MID MID0
11SP SP0
12PTB PTB0
13BEC BEC0
14STO STO0
  • v-hashadapu's avatar
    v-hashadapu
    6 months ago

    Hi unknown917 , Thank you for reaching out to the Microsoft Community Forum.

     

    From what you said, I assume sequence should restart for each Hub, that the row where Subset Name = Hub Name is the parent and must always be 0 and that all other rows under that Hub are true subsets that should be numbered 1, 2, 3 and so on and that there’s a clear column (like Subset ID) that defines the order of those subsets within each Hub. If so, then please try below DAX:

    Seq Order =
    VAR _Hub = 'Table'[Hub]
    VAR _IsParent =
    'Table'[Subset Name] = 'Table'[Hub Name]
    RETURN
    IF(
    _IsParent,
    0,
    RANKX(
    FILTER(
    'Table',
    'Table'[Hub] = _Hub &&
    'Table'[Subset Name] <> 'Table'[Hub Name]
    ),
    'Table'[Subset ID],
    ,
    ASC,
    DENSE
    )
    )

     

    If that doesn’t work, then we’d need a small but representative sample of your actual data that fully reproduces the issue, clarification on which column should drive the ordering, confirmation on whether Subset ID is unique within each Hub and any other details that would help us accurately replicate your scenario and give you the correct logic. Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

2 Replies

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    6 months ago

    Hi unknown917 , Thank you for reaching out to the Microsoft Community Forum.

     

    From what you said, I assume sequence should restart for each Hub, that the row where Subset Name = Hub Name is the parent and must always be 0 and that all other rows under that Hub are true subsets that should be numbered 1, 2, 3 and so on and that there’s a clear column (like Subset ID) that defines the order of those subsets within each Hub. If so, then please try below DAX:

    Seq Order =
    VAR _Hub = 'Table'[Hub]
    VAR _IsParent =
    'Table'[Subset Name] = 'Table'[Hub Name]
    RETURN
    IF(
    _IsParent,
    0,
    RANKX(
    FILTER(
    'Table',
    'Table'[Hub] = _Hub &&
    'Table'[Subset Name] <> 'Table'[Hub Name]
    ),
    'Table'[Subset ID],
    ,
    ASC,
    DENSE
    )
    )

     

    If that doesn’t work, then we’d need a small but representative sample of your actual data that fully reproduces the issue, clarification on which column should drive the ordering, confirmation on whether Subset ID is unique within each Hub and any other details that would help us accurately replicate your scenario and give you the correct logic. Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...