Parameter selection to table

[kodiejames13]

Hello everyone, I am fairly new to Power BI and I am trying to build a parameter in a semantic model with multiple clients that require different information showing in the table.

Is it possible to build a parameter that when you select a certain client, the table on the page will show only their respective data?

Example:

Client A when selected should show columns 1, 2, 3, 4 and 5.

Client B when selected should show columns 2, 4 and 5. Hiding columns 1 and 3.

Client C when selected should show columns 3, 4 and 5. Hiding columns 1 and 2.

Client D when selected should show columns 2 and 4. Hiding columns 1, 3 and 5.

This data will be sent via subscription email using Power BI report builder.

Any help would be great, thank you!

[SacheeTh]

Hi @kodiejames13 ,

Yes, you can achieve this in Power BI using OLS & RLS as @AnkitaaMishra said below,

I have edited this reply for paginate report as well below

I have done this with a combination of Field Parameters and Dynamic Column Visibility logic. Since your report is sent via subscriptions here's a step-by-step breakdown. (No idea that OLS or RLS will apply for each user via email)

---

1. Create a Field Parameter for Dynamic Column Selection

   1. Go to the Modeling tab > Select New Parameter > Choose Fields.
   2. Add the required columns (e.g., Column 1, Column 2, etc.).
   3. Rename the parameter table (e.g., ClientColumnParameter).
   4. Power BI will create a slicer for dynamic column selection.

2. Define a Supporting Table for Clients Create a table to map Clients to their respective columns.

   ClientColumnMapping = 
   DATATABLE(
       "Client", STRING,
       "ColumnsToShow", STRING,
       {
           {"Client A", "1,2,3,4,5"},
           {"Client B", "2,4,5"},
           {"Client C", "3,4,5"},
           {"Client D", "2,4"}
       }
   )

   This maps the client name to a comma-separated list of columns.

3. Build Logic for Column Visibility

   1. Create a calculated table or DAX measure to dynamically filter columns based on the selected client:

      SelectedColumns = 
      VAR SelectedClient = SELECTEDVALUE('ClientColumnMapping'[Client])
      RETURN
      SWITCH(
          SelectedClient,
          "Client A", "1,2,3,4,5",
          "Client B", "2,4,5",
          "Client C", "3,4,5",
          "Client D", "2,4",
          BLANK()
      )

4. Apply Filters Dynamically in the Table

   1. Use a Matrix Visual instead of a Table visual for flexibility.
   2. Place the Fields Parameter in Columns of the Matrix.
   3. Use a slicer for ClientColumnMapping to filter the selected columns dynamically.

5. Test Behavior

   1. When you select a client in the slicer, the columns should adjust to display only their respective values.

_____

Solution: Power BI Report Builder (Paginated Reports)

Since Power BI Report Builder does not support dynamic column hiding like Power BI Desktop, use conditional visibility logic:

1. Add a Parameter for Client Selection

   • In Report Builder, create a Client parameter with values "Client A," "Client B," etc.

2. Conditionally Hide Columns

   • In your report's Table:
     • Right-click on a column > Select Column Visibility.
     • Use the following expressions to show/hide columns:
       • Column 1: =IIF(Parameters!Client.Value = "Client A", False, True)
       • Column 2: =IIF(Parameters!Client.Value IN ("Client A", "Client B", "Client D"), False, True)
       • Repeat this for other columns with their respective logic.

3. Subscription Email

   • When you set up the email subscription, the chosen parameter values (Client A, B, etc.) will determine which columns are visible in the final emailed report.

[Anonymous]

Hi @kodiejames13 , hello all, thank you for your prompt reply!

Is there any progress on this issue?

If you find any answer is helpful to you, please remember to accept it.

It will help others who meet the similar question in this forum.

Thank you for your understanding.

[AnkitaaMishra]

Hi @kodiejames13 , 
If there are limited number of clients like A,B,C then you might check creating different Views using bookmarks or page navigation for each client and use it in combination with Row level security.
Or you can use object level security to hide the columns from end users using tabular editor.
https://learn.microsoft.com/en-us/fabric/security/service-admin-object-level-security?tabs=table
https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security

Thanks,
Ankita 

[pborah]

Hi @kodiejames13 , you can use row level security for this, although you'd have to prepare your data accordingly. 

https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security

[kodiejames13]

I should have mentioned(I've since updated the post) that this data will be sent via email using BI report builder using the semantic model.

[pborah]

Thanks @kodiejames13 are you using a semantic model stored in PBI service in Report Builder? If so you will need to define RLS in the dataset itself stored in service. It's unnecessarily complicated to implement RLS in Report Builder directly unless you are comfortable with VB code.

For our purposes we chose to implement it on SQL side as it was the most simplistic solution that worked for us when using paginated reports. 

IF the emailed RDL file will contain an embedded dataset, I'd heavily emphasize on creating separate datasets and reports for each client so as to absolutely prevent unwanted data leaks. That is assuming each of your clients are distinct organizations. Otherwise you are risking legal liability.