Need Help with Dax Formula

[ahmer_malick]

I am trying to create a Index . The criteria should we If Order Type is Equal to On Hand then 1 , Safety Stock 2. . Except these 2 criteria, if there is a sales order or a work order after this, it should count it in an asc order even its a past due order ( less than today)

RANKX (

    FILTER (

        'SUPPLY & DEMAND',

        'SUPPLY & DEMAND'[ITEM NUMBER] = EARLIER ( 'SUPPLY & DEMAND'[ITEM NUMBER] ) && 'SUPPLY & DEMAND'[WHS]=EARLIER('SUPPLY & DEMAND'[WHS])

    ),

    'SUPPLY & DEMAND'[SCHED PICK].[Date],

    , ASC

    , DENSE

)

[v-sgandrathi]

Hi @ahmer_malick,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

[v-sgandrathi]

Hi @ahmer_malick,

Has your issue been resolved?
If the response provided by @ryan_mayu  and @kushanNa  addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[ryan_mayu]

@ahmer_malick 

could you pls paste the data here (not the screenshot ) and also provide the expected output?

[kushanNa · Accepted solution]

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