Forum Discussion
Power BI dynamic visualizations
Power BI cannot work with encrypted Excel files.
Confidential - Microsoft Extended:DESCRIPTION Data is classified and protected. Microsoft Full Time Employees (FTE) and non-employees can edit, reply, forward and print. Recipient can unprotect content with the right justification.
Hi pk3130,
Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.
Thank you.
Hi v-kpoloju-msft ,
Thanks for the prompt reply.
1. You have a DataTable which is in Unpivoted form, But my data is pivoted form
Ex: Columns of my datatable are Col1,col2,col3 and so on.
Hi pk3130,
Thank you for bringing the follow-up question. Below I provided one workaround that might be resolve your query. Go through it once.
I understand that you are using a parameter-based slicer to allow users to select multiple columns (from a predefined set of 50), and you have successfully retrieved the first 10 selected columns using DAX logic. However, you are facing difficulty mapping each of these 10 selected columns dynamically to 10 separate table visuals.
Power BI does not currently support dynamic column binding in visualizations. This means that while DAX can calculate or return dynamic column names based on user selection, it cannot dynamically change the actual field displayed in a visual (such as a table or chart). Visual fields must be statically defined at design time.This is why the selected column names cannot be directly mapped to visuals the visuals require fixed schema references. The workaround is mentioned beow:
Unpivot the 50 columns into rows using Power Query (i.e., transform Col1–Col50 into Attribute and Value columns). Create a disconnected table listing all column names (Col1 to Col50) and use that in a slicer for user selection. Use DAX logic to assign an index (1 to 10) to the first 10 selected columns.
In each of the 10 visuals, filter the unpivoted data by matching the visual index (1–10) with the selected attribute. This simulates dynamic assignment each visual displays the values for one of the selected columns. Only the first 10 selected columns will be shown, and each one will appear in its respective table visual.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.Thanks v-kpoloju-msft for the solution.
bit hesitate in pivoting it as the data is going to be huge, Is it possible also to add dyamic table visualizations based on columns in SelectedTableColumns. For Example if SelectedTableColumns has 3 columns then then report automatically has to create 3 table visualizations
Example : lets says we created 10 table visualizations but user has populated data only for 6 columns then the rest 4 table visualizations are going to be empty.
Other issue that i observed is when i select one of the record in one of the visualization then the data in all the visualizations within the report will be filtered but with this approach that is not the case, Any thoughts on this ?Hi pk3130,
Thanks for the great follow-up. You have raised three very valid points especially around performance, visual flexibility, and native interactivity.
Performance concern with unpivoting large data: You are right unpivoting a wide table (like 50 columns) on a large dataset can increase memory usage. If your dataset is very large, I would recommend testing it on a sample slice to assess the impact.Alternatively, the Field Parameters feature (explained below) avoids the need to unpivot entirely.
Dynamic number of visuals based on selected columns: Power BI does not support creating visuals dynamically. However, you can simulate this by:- Pre-building 10 table visuals
- Applying a filter using a measure like ShowTableX to hide visuals with no data (if fewer than 10 columns are selected).
This approach gives users a clean experience by showing only the relevant visuals.
Cross-filtering between visuals: This is a known limitation with disconnected slicer-based setups or unpivoted data. Since these visuals do not share a natural relationship, clicking one does not filter the others.To retain native cross-filtering behaviour, I would recommend exploring Field Parameters a newer feature that allows users to select multiple columns in a slicer and display them in a single table visual, while retaining native interactivity.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.