Calculated Table with TOPN

[Anonymous]

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? 

[Ashish_Mathur]

Hi,

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

[ppm1 · Accepted solution]

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]

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