Dynamically Change Matrix Rows Based on Slicer Selection in Power BI from Multiple tables

[danishrizvi]

I am working on a Power BI report where I have three datasets, each representing data for a different website. The datasets have identical column structures but contain different values. Here is an example of the structure:

- Site 1 Table: Contains data for Website 1 (e.g., Keywords, Clicks, Impressions, etc.)
- Site 2 Table: Contains data for Website 2 (same columns but different values)
- Site 3 Table: Contains data for Website 3 (same columns but different values)

I want to create a single Matrix Visual that dynamically displays the rows (specifically the "Keywords" column) based on the slicer selection. For example:
- If I select Site 1 in the slicer, the Matrix should display the Keywords from the Site 1 dataset in the rows.
- If I select Site 2, it should show the Keywords from the Site 2 dataset, and so on.

I understand that measures in Power BI are designed to return scalar values, while I need to dynamically show a column of text values (i.e., the "Keywords" column) in the Matrix Visual rows. I have tried creating measures to dynamically switch between tables, but measures do not support returning multiple text values to populate the rows.

What I've Tried
Using Measures: I created measures to dynamically calculate numeric values (e.g., Clicks, Impressions) for the Matrix Visual, and they work fine for the columns or values section. However, this approach does not work for dynamically populating the rows with text data (Keywords).

Is there a way to dynamically switch the rows in the Matrix Visual (to display "Keywords") based on slicer selection?

[N0EL · Accepted solution]

Hey @danishrizvi 

Thinking maybe Field Paramters could work.

Here's a quick example I created and a link to a Microsoft resource page:   Implement data translation using field parameters

[rohit1991]

To dynamically change Matrix rows based on slicer selection:

1. Combine Tables:
   Append Site 1, Site 2, and Site 3 tables into a single table in Power Query. Add a Site column to identify the source.

   Example table structure:
   
   

   Keywords Clicks Impressions Site

   Keyword 1

   100

   1000

   Site 1

   Keyword A

   50

   300

   Site 2

   1. Create a Slicer:
      Use the Site column in a slicer for site selection.

   2. Configure Matrix Visual:

      • Add Keywords to Rows.
      • Add metrics (e.g., Clicks, Impressions) to Values.

   3. Optional: Enable single-select in the slicer for cleaner filtering.

   This approach dynamically updates rows based on slicer selection.

[danextian]

Hi @danishrizvi 

If you need to dynamically display only a single column, using Field Parameters, as suggested by @danishrizvi,  will work. However, this approach won't apply to multiple columns. Since the datasets share a similar structure but differ in values, consider combining them into one table using the append feature in the query editor. Make sure to add a column in each original query to identify the site they correspond to, if this information isn't already included.

[N0EL · Accepted solution]

Hey @danishrizvi 

Thinking maybe Field Paramters could work.

Here's a quick example I created and a link to a Microsoft resource page:   Implement data translation using field parameters

[danishrizvi · Accepted solution]

I tried this approach, and it worked flawlessly. The solution is straightforward: I used a field parameter and added all the required columns to it. I renamed the field parameters based on the slicer I was using on the page. Then, I created a 1:* relationship between the field parameter and my slicer table to filter the data. I ensured this relationship was active so the values displayed correctly based on the slicer selection. 

I used the field parameter in my rows, allowing the table's column to dynamically change based on the slicer selection.

[Ritaf1983]

Hi @danishrizvi 

please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community