Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 🙂

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 🙂

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 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.