Forum Discussion
Extension to filtering columns in a table
Hello all,
I have found resources for filtering column names in a table using parameter fields. Which basically allows the user to select which columns they would like to see in a table. In my report I know which columns should be visible depending on the category, therefore I am wondering if it is possible to have a category slicer that filters the parameter field that then filters out the columns from a table.
Table example
For example if this is the table visual, I'd like to be able to select 'Apple' and then only have the apple columns, and not require the user to do it themselves. Alternatively this problem is also the same as hiding columns that are fully empty. I'd prefer not to use bookmarks because in my real report I have like 15 different categories, and it would be nice to avoid having a bunch of buttons.
Stuff I tried:
* Creating a separate item_type table and adding a relationship from that to the parameter fields table. This however didn't work, as power bi did not like filtering from 1 to many (No matter which way i set up the relationship, it would only allow a parameter field filtering the item_type, and not item_type filtering down to a few columns in parameter field table)
I have made a report that has this example and wondering if this is possible or if i'm stretching power bi capabilities too much and should go back to having a bookmark solution. Unfortunately I can't see something that lets upload power bi files
You can create a calculated column in the fields parameter table that categorizes the parameter value and use that in a slicer. Note: you can use this to select which columns visible but it won't work as expected if if both the calc and parameter columns are added in a visual.
6 Replies
- danextian
Super User
You can create a calculated column in the fields parameter table that categorizes the parameter value and use that in a slicer. Note: you can use this to select which columns visible but it won't work as expected if if both the calc and parameter columns are added in a visual.
Thanks that does work, and it's almost there. What about if there are some parameters that are shared by categories. Let's say there was a metadata.name column that should apply to multiple categories. That I think wouldn't work with the solution you have here as you wouldn't be able to put multiple names in the category column.
Technically it would be possible to create a column like metadata.apple_name and metadata.banana_name but if you're gonna be working with 15+ categories, this would feel like a waste of space and lots of menial work.
- bhanu_gautam
Create a table that lists all possible columns you want to show/hide.
ParameterTable =
DATATABLE(
"ColumnName", STRING,
{
{"AppleColumn1"},
{"AppleColumn2"},
{"BananaColumn1"},
{"BananaColumn2"},
...
}
)Create a table that lists all categories.
CategoryTable =
DATATABLE(
"Category", STRING,
{
{"Apple"},
{"Banana"},
...
}
)Create a relationship between your main data table and the ParameterTable based on the column names.
Create a measure that determines whether a column should be visible based on the selected category.
IsVisible =
VAR SelectedCategory = SELECTEDVALUE(CategoryTable[Category])
RETURN
IF(
CONTAINSSTRING(SELECTEDVALUE(ParameterTable[ColumnName]), SelectedCategory),
1,
0
)Use the IsVisible measure to conditionally format the columns in your table visual. You can do this by setting the column visibility based on the measure.
Add a slicer to your report using the CategoryTable. This slicer will allow users to select a category, which will then filter the columns in the table visual based on the IsVisible measure.
Hi drsomething123 ,
Thank you for reaching out to the Microsoft Fabric Forum Community.
And also thanks to danextian and bhanu_gautam for prompt ans useful response.
Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.
If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful this will benefit others in the community as well.
Best regards,
Prasanna Kumar
Hi drsomething123,
We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.
If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution it helps others who might face a similar issue.
Warm regards,
Prasanna Kumar
Thanks for checking in, I'm not sure what the procedure is, but I will only be able to check the solution out tomorrow or next week.
