Filter different columns to display in the table.

[Elemesey04]

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. 

https://www.dropbox.com/scl/fo/cwmscntewelfqssuqnidu/AP1a8-lhTO79t8UvrWtq8MY?rlkey=9jn2fpducnf2icb5a...

Thank you.  

[danextian · Accepted solution]

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.

[DNMAF · Accepted solution]

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?

[Shubham_rai955]

Here’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.

[DNMAF · Accepted solution]

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?

[Elemesey04]

Hi @DNMAF , yes this what I mean. Thank you 😊 can I have a copy of your file? Check how you do this.

[DNMAF · Accepted solution]

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.

[Elemesey04]

I appreciate this and thank you for your support @DNMAF 😊

[v-tsaipranay]

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.

[Elemesey04]

Hello @v-tsaipranay Yeah, I already received assistance from @DNMAF and @danextian. Thank you!😊

[danextian · Accepted solution]

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.

[Elemesey04]

Thank you for your great assistance @danextian 😀 I marked this as solution in my post.

[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

[Elemesey04]

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?

[DNMAF]

Hi @Elemesey04 , sounds like field parameters could be a solution:

1. Create a field parameter: Modeling->New parameter->Fields
2. Give the parameter a name
3. Drag all the columns you want to "Add and reorder fields" 
4. Leave the "Add slicer to this page" checked
5. Assign for example your region column to a new table visual
6. Assign the only column from your newly created field parameter table to the table visual
7. Play with the slicer to selected or deselect columns

Does that work for you? Do you need any further assistance?

[Elemesey04]

Hello @DNMAF , 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?