cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dinesh_ch
Regular Visitor

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

1 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
)

asds.png

Regards,
Zubair


View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

@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.

Regards,
Zubair


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
)

asds.png

Regards,
Zubair


View solution in original post

Thank you so much Zubair. It worked.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.