Dynamic table

[AMZ]

Hello,

I'm trying to create a dynamic table in Power BI.

I'll give the context:

I have an Excel file with several data tables.
I have a Power BI dashboard with a visual Filter and each filter corresponds to a table in the Excel file. I would like the table to change depending on the filter selected, taking the right data from the right table in the Excel file (depending on the filter selected on the visual, the table should take the right table and display the right data on the report).

My idea:

I thought of creating a calculated table with a formula that would say: "If on visual X, filter Y is selected then my table is T1 otherwise my table is T2." Something like that. In my idea, I need to specify exactly which visual it is, either with the exact name of the visual, or with a unique id.

If this is not possible, can you suggest an alternative solution?

Thanks in advance

[123abc]

Here's a general guideline on how you can approach this:

1. Import all Tables: Import all the tables from your Excel file into Power BI.

2. Create Relationships: Ensure that there are relationships established between the tables. This is typically done by linking columns that contain similar data in different tables.

3. Use DAX Measures: Create DAX measures that aggregate data based on the selected filter. For example, if you have a Date filter, you might create a measure like this:

TotalSales = CALCULATE(SUM('Sales'[SalesAmount]), 'Date'[Date] = SELECTEDVALUE('Date'[Date]))

1. In this example, the measure calculates the total sales amount, but it filters the data based on the selected date in the Date filter.

2. Create Table Visuals: Instead of creating a separate table for each filter, create a single table visual that uses the measures you've created. The table visual will dynamically display data based on the filter selection.

3. Set Slicers or Filters: Use slicers or filters to allow users to select the values they want to filter on. Power BI will automatically apply these filters to your visuals.

By using relationships and DAX measures, you can create a dynamic report that adapts to the selected filters without the need for a separate calculated table for each scenario.

If your scenario is more complex and requires dynamically changing the entire structure of the table based on the selected filter, you might need to consider using Power Query M functions to dynamically modify your data model. However, for many cases, the approach described above should be sufficient.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.