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 ha...
  • 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-...