Forum Discussion
pass slicer value to Filter inside CalculatedTable expression
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
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 )
4 Replies
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.
Thanks for the response.
Can you please help me in formulatig the query using measure to achieve the functionality.
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 )
