cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yudhi5
Frequent Visitor

How to filter table using preselected slicer text value

Hi all,

I have initial data table "dummy" consist of (minimum) two column in which one of them can be used as a filter. The filter set is in column 'country' and expect the user to either select the country, either one (ie. NO), more than 1 (ie. NO, MY), or none mean all selected to get filtered table based upon it.

 

this is the code that i put at the filtered "table" expecting user input in slicer. Seems like this is simple coding, i try to find references related to it, but still got no luck. Later I'm gonna use the filtered table for further measure so that I need to filter the initial table.
if someone can help to fix this. 

Table =
VAR __selectedValue = SELECTEDVALUE(CountrySlice[Value])
RETURN
CALCULATETABLE(dummy,FILTER(ALL(dummy),dummy[country]== __selectedValue))

 

 

 

Thank you

 

 

filter table using preselected text slicer valuefilter table using preselected text slicer value

 

 

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

but still couldnt create the filtered table from the initial 'dummy'. perhaps you have any better idea on that?

In general it is technically not possible to interact with the slicer and change values in the underlying dataset table (in this case filter for a single country). You can do that with Power Query parameter, but users will not have access to it from the report level, see more here:

https://community.powerbi.com/t5/Desktop/Passing-Slicer-Selection-to-Query/td-p/1136501

Why do you need subset of dummy as a materialized table? Formula engine can materialize  it on the fly if it's used in a measure, e.g. inside CALCULATE, but duplicating the subset based on a slicer is not gonna work.



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

3 REPLIES 3
yudhi5
Frequent Visitor

HI @Stachu 
Thanks for the reply. yes, seems the variable that i expect to link user input from the slicer is not working here.
So far the 'Table' is filtered correctly when i key-in the country code:

 

CALCULATETABLE(dummy,FILTER(ALL(dummy),dummy[country]=="MY"))

 

Talking about the join, i think i got your idea to create a relationship. this is working as it can narrow down the data in visual based upon user selected country. but still couldnt create the filtered table from the initial 'dummy'. perhaps you have any better idea on that?

filtered table using join relationshipfiltered table using join relationship
Attached the file.
filtered_table.pbix 

Thank a lot.

Cheers 🍻

Stachu
Community Champion
Community Champion

but still couldnt create the filtered table from the initial 'dummy'. perhaps you have any better idea on that?

In general it is technically not possible to interact with the slicer and change values in the underlying dataset table (in this case filter for a single country). You can do that with Power Query parameter, but users will not have access to it from the report level, see more here:

https://community.powerbi.com/t5/Desktop/Passing-Slicer-Selection-to-Query/td-p/1136501

Why do you need subset of dummy as a materialized table? Formula engine can materialize  it on the fly if it's used in a measure, e.g. inside CALCULATE, but duplicating the subset based on a slicer is not gonna work.



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Stachu
Community Champion
Community Champion

Calculated tables are calculated only on refresh, they do not change with the slicer selection. They also are calculated in the most general filter context (no filters at all), which means that

VAR _selectedValue = SELECTEDVALUE(CountrySlice[Value])

always returns blank, which then makes 'Table' an empty table.

 

Why not just create join between 'CountrySlice' and 'dummy'? That would generate visual (so essentially a query) which will filter the 'dummy' table according to the selection you made.

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors