0

I want to make a measure that returns the rank of the current Reason×Resource by [Qty_Sum], respecting page/report slicers.

    Qty_Sum =
[Qty_2025_W38] + [Qty_2025_W39] + [Qty_2025_W40]
DAXQty_2025_W37 =
SUMX (
    CALCULATETABLE (
        fct_stock_movements,
        KEEPFILTERS ( dim_date[calendar_week_year_label] = "2025-W37" )
    ),
    COALESCE ( fct_stock_movements[quantity], 0 )
        * COALESCE ( RELATED ( dim_cost[Total Cost] ), 0 )
)
  • Working baseline (table expression): Built

    _tmpTbl = ADDCOLUMNS(SUMMARIZE(ALLSELECTED(fct_stock_movements), dim_reason[reason_code], dim_resource[resource_code]), "__Qty", CALCULATE([Qty_Sum])) and added "__Rank" = RANKX(_tmpTbl, [__Qty], , DESC, DENSE);

this produced correct, varied ranks inside the virtual table. But it wasn't affected by the context window.

  • Current best attempt:
Rank Qty (Reason×Resource) = 
VAR _value = [Qty_Sum]
VAR _universe =
    ADDCOLUMNS (
        SUMMARIZE (
            CALCULATETABLE (
                ALLSELECTED ( fct_stock_movements ),
                REMOVEFILTERS ( dim_reason ),
                REMOVEFILTERS ( dim_resource )
            ),
            dim_reason[reason_code],
            dim_resource[resource_code]
        ),
        "__Qty", CALCULATE ( [Qty_Sum] )
    )
VAR _set = FILTER ( _universe, NOT ISBLANK ( [__Qty] ) )
RETURN
IF ( ISBLANK ( _value ), BLANK (), RANKX ( _set, [__Qty], _value, DESC, DENSE ) )

Sample Table

Obviously we want to see a clean context 1,2,3,4,5,etc but instead we get the strange 1,1,2,1,1 behaviour.

Any help would be much appreciated!

2
  • Can you put your Qty_Sum measure as well Commented Oct 16 at 10:32
  • Sure thing: Qty_Sum = [Qty_2025_W38] + [Qty_2025_W39] + [Qty_2025_W40] All the week measures are like so: DAX Qty_2025_W37 = SUMX( CALCULATETABLE( fct_stock_movements, KEEPFILTERS( dim_date[calendar_week_year_label] = "2025-W37" ) ), COALESCE( fct_stock_movements[quantity], 0 ) * COALESCE( RELATED( dim_cost[Total Cost] ), 0 ) ) Commented Oct 16 at 14:36

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.