Forum Discussion
Filter different columns to display in the table.
Hi guys, is it possible to have a slicer that includes multiple columns, and allows selecting which ones will be shown in the table? Also, the target type I choose should apply to the accomplishment percentage as well.
Thank you.
Hi Elemesey04
If you want the target type to appear beside Sales, use a table visual instead, since only scalar values can go into the Values area of a matrix. Adding a non-numeric field to the matrix requires it to be aggregated (for example, using Count, First, or Last).
As others have suggested, you can use field parameters to control which columns are visible. Field parameters can also be used to display both the target type and the accomplishment rate.
Please see the attached pbix.
Hello Elemesey04 ,
most probably this is possible, but I'm still not completely sure what you exactly need.
Perhaps a small demo will help us to find out:
There is one slicer to select the target type including the original target. Depending on this slicer the columns "Target Type", "Target" and "Accomplish Rate %" are calculated.
The definition of the rows of the matrix and their hierarchy depends on the "Group By" slicer. You can even change the hierarchy levels by changing the order of selection.
Is it that what you want?
Hi Elemesey04,
yes, you can😊 You find the link at the end of this posting.
Here are a few tipps on how I procesded:
- I started with your sample file
- Removed some empty rows from your two fact table using Power Query
- You should change back the path to the excel source file according to your needs
- Created a dimension table for Product Groups using DAX
- Created a date table using DAX
- Defined relationships between your two fact tables and the newly created tables
- Defined a measure table using "Enter data" (and removing the empty column after creating the first measure)
- Created a field parameter for Target Type including a slicer
- Created a field parameter for Group By including a slicer
- Changed the slicer settings a bit (e.g. only single selection for Target Type)
- Created the necessary measures
- Assigned "Group By" to the rows of the matrix visual
- Assigned the measures "Sum of Sales", "Target Type", "Sum of Target by Type" and Accomplish Rate %" to the Values
https://my.hidrive.com/lnk/F323wp2To
Hope that works for you. I you are fine with the solution I would be happy if you would accept it as such.
13 Replies
Hans-Georg_PulsSuper User
Hi Elemesey04 , sounds like field parameters could be a solution:
- Create a field parameter: Modeling->New parameter->Fields
- Give the parameter a name
- Drag all the columns you want to "Add and reorder fields"
- Leave the "Add slicer to this page" checked
- Assign for example your region column to a new table visual
- Assign the only column from your newly created field parameter table to the table visual
- Play with the slicer to selected or deselect columns
Does that work for you? Do you need any further assistance?
Hello Hans-Georg_Puls , thank you for your support 😊 However, I need the monthly and product group reports summarized in a Table Matrix visual. Do you think this is possible?
- PhilipTreacy
Hi Elemesey04
So what you are trying to do is only show the columns of data selected in the slicer?
You can use a Field Parameter for this.
From the Modelling menu -> New Parameter -> Fields and add the Fields (columns)
make sure the check box 'Add slicer to this page' is checked and click Create
Create a table and add the parameter you just created, use the slicer to display the columns you want.
I answered similar question here Solved: Re: column headers as a slicer - Microsoft Fabric Community
Regards
Phil
Hello PhilipTreacy , thank you for your support 😊 However, I need the monthly and product group reports summarized in a Table Matrix visual. Do you think this is possible?
- danextian
Hi Elemesey04
If you want the target type to appear beside Sales, use a table visual instead, since only scalar values can go into the Values area of a matrix. Adding a non-numeric field to the matrix requires it to be aggregated (for example, using Count, First, or Last).
As others have suggested, you can use field parameters to control which columns are visible. Field parameters can also be used to display both the target type and the accomplishment rate.
Please see the attached pbix.
Thank you for your great assistance danextian 😀 I marked this as solution in my post.
Hi Elemesey04 ,
Thank you for reaching out to the Microsoft fabric community forum.
Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided by danextian . If you still require support, please let us know, we are happy to assist you.
Thank you.
Hello Anonymous Yeah, I already received assistance from @DNMAF and @danextian. Thank you!😊
- Hans-Georg_Puls
Hello Elemesey04 ,
most probably this is possible, but I'm still not completely sure what you exactly need.
Perhaps a small demo will help us to find out:
There is one slicer to select the target type including the original target. Depending on this slicer the columns "Target Type", "Target" and "Accomplish Rate %" are calculated.
The definition of the rows of the matrix and their hierarchy depends on the "Group By" slicer. You can even change the hierarchy levels by changing the order of selection.
Is it that what you want?
Hi Hans-Georg_Puls , yes this what I mean. Thank you 😊 can I have a copy of your file? Check how you do this.
- Hans-Georg_Puls
Hi Elemesey04,
yes, you can😊 You find the link at the end of this posting.
Here are a few tipps on how I procesded:
- I started with your sample file
- Removed some empty rows from your two fact table using Power Query
- You should change back the path to the excel source file according to your needs
- Created a dimension table for Product Groups using DAX
- Created a date table using DAX
- Defined relationships between your two fact tables and the newly created tables
- Defined a measure table using "Enter data" (and removing the empty column after creating the first measure)
- Created a field parameter for Target Type including a slicer
- Created a field parameter for Group By including a slicer
- Changed the slicer settings a bit (e.g. only single selection for Target Type)
- Created the necessary measures
- Assigned "Group By" to the rows of the matrix visual
- Assigned the measures "Sum of Sales", "Target Type", "Sum of Target by Type" and Accomplish Rate %" to the Values
https://my.hidrive.com/lnk/F323wp2To
Hope that works for you. I you are fine with the solution I would be happy if you would accept it as such.
Shubham_rai955Here’s a practical solution for your Power BI report:
Use Power BI’s "Field Parameters" feature to let users choose which columns appear in the table. Create a parameter table for your columns and add a slicer linked to it. When users select from the slicer, only those columns show in the table.
For the accomplishment percentage to use the selected Target Type, create a DAX measure. Use SELECTEDVALUE([Target Type]) within your measure to dynamically adjust the calculation based on slicer selection.
Put the parameter slicer and the Target Type slicer on your report page, linked to your table and measures.
Users will be able to pick columns (using the Field Parameter slicer), and select a target type, which is then applied automatically in the accomplishment percentage calculation.
This method provides both flexible column visibility and dynamic calculation , no custom visuals needed.
