Dynamic Table Columns Based on Table Name Slicer

Question

Hi all,&nbsp;I have a scenario in Power BI and need some guidance:Scenario:I have 10-12 different tables at source.Each table has different columns (not the same schema).I want to create a single visual (table/matrix) in Power BI where:There is a slicer with the table names.When a user selects a table from the slicer, the visual dynamically shows the columns of that selected table.What I’ve tried:I considered unioning the tables in Power Query, but the tables have different columns, so that didn’t work.I also looked at Field Parameters, but that only works when columns are identical.Challenge:Power BI table/matrix visuals require columns to be predefined. I need the columns to change dynamically based on slicer selection, which seems unsupported natively.Questions:Is it possible to achieve a fully dynamic table where columns change based on slicer selection?If not, what’s the best workaround in Power BI for this scenario?Are there any custom visuals&nbsp;that can solve this?I’d really appreciate guidance, examples, or links to similar scenarios.&nbsp;Thanks in advance!

amitchandak · Answer

Anonymous&nbsp;, Create field Parameters, edit manually if needed for column name. Add a new calculated column Table name, now you can use that with table name filter&nbsp;Please check the attached file after the signature&nbsp;Table name remove single can be there inside&nbsp;SUBSTITUTE, also I have manually added last two rows

amitchandak · Answer

Anonymous&nbsp;, You have all of them to field parameters , if they have same name add manually. Chceck how I calculated a new column, in field parameter table&nbsp;Example&nbsp;
Parameter = {
    ("Brand", NAMEOF('Item'[Brand]), 0),
    ("Category", NAMEOF('Item'[Category]), 1),
    ("Sub Category", NAMEOF('Item'[Sub Category]), 2),
    ("Item Id", NAMEOF('Item'[Item Id]), 3),
    ("City", NAMEOF('Geography'[City]), 4),
    ("State", NAMEOF('Geography'[State]), 5),
    ("City Id", NAMEOF('Geography'[City Id]), 6),
    ("Name", NAMEOF('Customer'[Name]), 7),
    ("Customer Id", NAMEOF('Customer'[Customer Id]), 8),
    ("Customer City", NAMEOF('Customer'[City]), 9), // Added Manually 
    ("Customer State", NAMEOF('Customer'[State]), 10)// Added Manually 
}
&nbsp;
Added a new column for filter in field parameter table only&nbsp;
Table = left(SUBSTITUTE(Parameter[Parameter Fields], "'",""), SEARCH("[", Parameter[Parameter Fields])-3)
&nbsp;Code might need changes for table code&nbsp;

&nbsp;

anonymous · Answer

And then can it be used it in a table visual ? or a matrix visual?
Also will the field parameter work here, because in each source table, I have different columns, and I need to display the exact replica of that column, as per selection of the table from slicer?

amitchandak · Answer

Anonymous&nbsp;,&nbsp;Please check the attached file after the signature
Table name remove single can be there inside SUBSTITUTE, also I have manually added last two rows

anonymous · Answer

amitchandak&nbsp;Thanks for your responses. But it'll not work in my scenerio as I have three tables-&nbsp;1. Missing TRx From Previous LoadsTXN_IN_DATEMISSING_FROM_DATEREMS_TRANSACTION_IDLONGITUDINAL_PATIENT_IDREMS_PATIENT_IDHUB_PATIENT_IDREASON1234567&nbsp;2. Duplicate TRxREMS_TRANSACTION_IDOCCURRENCE_COUNT12&nbsp;3.&nbsp;HUB vs RDA IndicationREMS_TRANSACTION_IDLONGITUDINAL_PATIENT_IDPATIENT_TRX_ORDER_NUMBERRDA_INDICATIONPROPOSED_INDICATIONDERIVED_INDICATIONHUB_INDICATION1234567&nbsp;Now, there will be a slicer which will contain the table names (as given above).And if someone selects any table from the slicer, the columns with relevant values should gets displayed in a tabular format.

Forum Discussion

Dynamic Table Columns Based on Table Name Slicer

7 Replies

Related content

Dynamic Subtitle with Hierarchy Slicers

Slicer for filtering several columns

Power BI Dynamic Filter/Slicer

Show slicers dynamically

Dynamic Column headers