Forum Discussion
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 value
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.
3 Replies
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.
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 relationship
Attached the file.
filtered_table.pbix
Thank a lot.
Cheers 🍻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.