Need Help with Dax Formula

Hi ahmer_malick 

 

Do you want to keep the index fixed once it has been assigned to an order type? If that’s the case, please check whether this M code (Query1) approach works for you instead of DAX

let
    // --- Source Table ---
    Source = #"SUPPLY & DEMAND",

    // --- Sort to make results consistent ---
    #"Sorted Rows" = Table.Sort(Source, {
        {"ITEM NUMBER", Order.Ascending},
        {"WHS", Order.Ascending},
        {"SCHED PICK", Order.Ascending}
    }),

    // --- Assign base indexes for fixed order types ---
    #"Added BaseIndex" = Table.AddColumn(
        #"Sorted Rows",
        "BaseIndex",
        each 
            if [ORDER TYPE] = "ON HAND BALANCE" then 1
            else if [ORDER TYPE] = "SAFETY STOCKS" then 2
            else null,
        Int64.Type
    ),

    // --- Create distinct list of remaining order types per ITEM+WHS ---
    #"DistinctOrderTypes" = 
        Table.Distinct(
            Table.SelectColumns(
                Table.SelectRows(#"Added BaseIndex", each [BaseIndex] = null),
                {"ITEM NUMBER", "WHS", "ORDER TYPE"}
            )
        ),

    // --- Add index starting from 3 for other order types ---
    #"Add Type Index" =
        Table.Group(
            #"DistinctOrderTypes",
            {"ITEM NUMBER", "WHS"},
            {
                {"TypeList",
                    each 
                        Table.AddIndexColumn(_, "TypeIndex", 3, 1, Int64.Type)
                }
            }
        ),

    // --- Expand TypeIndex for each group ---
    #"Expanded Types" = Table.ExpandTableColumn(#"Add Type Index", "TypeList", {"ORDER TYPE", "TypeIndex"}),

    // --- Join back to main table ---
    #"Merged Index" = Table.NestedJoin(
        #"Added BaseIndex",
        {"ITEM NUMBER", "WHS", "ORDER TYPE"},
        #"Expanded Types",
        {"ITEM NUMBER", "WHS", "ORDER TYPE"},
        "JoinIndex",
        JoinKind.LeftOuter
    ),

    #"Expanded Join" = Table.ExpandTableColumn(#"Merged Index", "JoinIndex", {"TypeIndex"}, {"TypeIndex"}),

    // --- Final GroupedIndex column ---
    #"Added GroupedIndex" = Table.AddColumn(
        #"Expanded Join",
        "GroupedIndex",
        each if [BaseIndex] <> null then [BaseIndex] else [TypeIndex],
        Int64.Type
    ),

    // --- Cleanup ---
    #"Removed Extra Columns" = Table.RemoveColumns(#"Added GroupedIndex", {"BaseIndex", "TypeIndex"})
in
    #"Removed Extra Columns"

 

I have attached the sample pbix file for your reference