Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculated Table with TOPN

Trying to create a calculated table that lists Top 50 stores by a KPI (Waittime). I use the following DAX and it always return the list for all time. I need a dynamic table that chnages the list by the date range slicer on the report.

 

Bottom_50_Stores_OWT = 
VAR basetable =
    CALCULATETABLE (
        ADDCOLUMNS (
            DISTINCT ( 'Store Alignment'[STR_NUM] ),
            "waittime", CALCULATE ( [Wait Time], ALLSELECTED ( 'Date'[ACTL_DT] ) )
        ),
        'Div Letter Conversion'[Div Ltr] <> "X"
    )
VAR TOP50 =
    CALCULATETABLE ( TOPN ( 50, basetable, [waittime] ) )
RETURN
    basetable

 

 any possible solutions? 

Need Help
Tips and Tricks
  • ppm1's avatar
    ppm1
    3 years ago

    DAX calculated tables and columns do not update with slicer changes (only at data refresh). You need to build your table expression into a table variable in a measure.

     

    Pat

     

3 Replies

  • ppm1's avatar
    ppm1
    Icon for Solution Sage rankSolution Sage
    3 years ago

    DAX calculated tables and columns do not update with slicer changes (only at data refresh). You need to build your table expression into a table variable in a measure.

     

    Pat

     

  • Padycosmos's avatar
    Padycosmos
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hope this helps: In the Modelling Tab >> New Table>> and type the following formula. 

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Share some data to work with and show the expected result.  State the conditions very clearly.