pass slicer value to Filter inside CalculatedTable expression

[dinesh_ch]

Hello,

I have a table with below structure and slicer with MonthNum

Metric  MonthNum

10         1

40         2

70         3

100       4

20         5

Based on the selection of single slicer value, i need to get above table data having MonthNum less than or equal to slicer selected value.

If 3 is selected in slicer, i need to populate table dynamically as below

Metric  MonthNum

10         1

40         2

70         3

Tried with this DAX expression, but its not giving correct output.

DynamicTable = CALCULATETABLE('Actualtable',FILTER('Actualtable','Actualtable'[MonthNum] <= SELECTEDVALUE('Slicer'[MonthNum]))

Can you please help.

Thanks

[Zubair_Muhammad]

@dinesh_ch

Slicers do not affect calculated tables or calculated columns since these are computed when data is first loaded and are static until data is refreshed.

Try using MEASURES instead.

[dinesh_ch]

Thanks for the response.

Can you please help me in formulatig the query using measure to achieve the functionality.

[Zubair_Muhammad · Accepted solution]

Hi @dinesh_ch

Try these steps.

Please see attached file as well

First a calculated table which will be used as slicer

Months =
VALUES ( YourTable[MonthNum] )

 Now a MEASURE to be used as VISUAL filter

FilterMeasure =
IF (
    SELECTEDVALUE ( YourTable[MonthNum] ) <= SELECTEDVALUE ( Months[MonthNum] ),
    1
)

[dinesh_ch]

Thank you so much Zubair. It worked.