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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PetrK
Helper II
Helper II

Dynamic number of columns in table with fix columns

Hi, 

I created visual table with dynamic number of columns according user selection. Base on https://www.youtube.com/watch?v=6jeSIRpjv0M

 

But I need create table where will be everytime visible 3 specific columns and next columns can be add by used from slicer as is displayed on this video.

 

example:

I have dataset with 10 columns A, B, C, D, ...., J and I want to create table/matrix where will be every time on page load displayed columns A,B,C and next columns like D, E, F, G, ... can be add by user.

 

Is there any solution?

 

Thank you for help 

1 ACCEPTED SOLUTION
miTutorials
Super User
Super User

You can also do this easily using what if parameter. Add the fields to the table which you want to be fixed. For example : Country , Category , Sales. Then add the field created fom What if Parameter and the slicer will let you choose what columns to display based on requirement.

 

If you do not know how to setup what if Parameter, see my detailed video below.

 

New Feature: Slicer to choose which columns to display on chart using Fields Parameter in PowerBI 

View solution in original post

5 REPLIES 5
miTutorials
Super User
Super User

You can also do this easily using what if parameter. Add the fields to the table which you want to be fixed. For example : Country , Category , Sales. Then add the field created fom What if Parameter and the slicer will let you choose what columns to display based on requirement.

 

If you do not know how to setup what if Parameter, see my detailed video below.

 

New Feature: Slicer to choose which columns to display on chart using Fields Parameter in PowerBI 

Hi @miTutorials, I like this sollution.

Is there a way to don't display any column in table if in slicer is not selected any one?

Now if there is no any selection in slicer, all columns are displayed. It can be confusing for user.

As a work around you can add a blank column and hide that blank field in the table and leave that checked on the slicer, when you publish the report to service.

ichavarria
Solution Specialist
Solution Specialist

Yes, you can create a table or matrix visual in Power BI that always shows three specific columns (A, B, and C) and allows the user to add additional columns based on a slicer selection.

Here's one way you can do it:

  1. Create a new table with a single column that contains the names of the columns you want to display in your visual. For example, you could create a table called "Columns" with columns "Column Name" and "Display Order", and add rows for columns A, B, and C:

    ichavarria_0-1678134231575.png

     

  2. Create a new measure that dynamically calculates the columns to display based on the user's slicer selection. For example, you could create a measure called "Selected Columns" with the following formula:
    ichavarria_1-1678134280601.png

     

    This measure uses the SELECTCOLUMNS function to create a new table that contains the column names and their display order from the "Columns" table, based on the slicer selection. The RETURN statement then uses another SELECTCOLUMNS function to create a new table that only contains the column names.

    1. Create a new table or matrix visual, and add the "Selected Columns" measure to the Columns field well. This will dynamically display the columns based on the user's slicer selection.

    2. Manually add the three specific columns (A, B, and C) to the visual, and arrange them in the order you want them to appear. You can also format these columns as needed.

    With this approach, the visual will always show the three specific columns (A, B, and C) in the order you specified, and the user can add additional columns based on their slicer selection.

 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

I did everything as you suggested, but your solution contains syntax errors

igorplotnikov_0-1698875611494.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.