Use value from slicer to reference another table and a specific column in that table

[SteffenG]

Hi everyone,

apologies if this following question had been asked but I couldn't find anything in the forums. 

I have a large amount of data with thousands of tables in a sql db, each of them with 80,000 rows and hundreds of columns. Each of those tables has the same rows so if you would put it all together, you would basically have one matrix with 80,000 rows and 100,000 columns or so.  

My question is how I can use slicers etc. to pick specific rows and columns across all those tables and then visualize the values from those fields?

The row part is easy since I have a good logic already to easily filter down to the desired rows. The column part would be easy as well if I would have all those 100,000 columns in a single matrix since I have a good logic to filter down all those columns and pick the exact ones I want. I could just unpivot the columns and search through them that way. HOWEVER, it's probably a terrible idea to build a matrix with 100,000 columns and to unpivot everything 😉 since the dataset would become way to large. So, I need a way to search across tables and tell Power BI which table(s) to pull from and which column(s) within those tables. 

The first screenshot below shows the simplified logic of my thousands of tables. And the second screenshot shows how far I've taken this simplified model in PBI. Happy to attach the PBIX as well but can't figure out how. Any suggestion would be highly appreciated!!

Thanks in advance

Steffen

Simplified file structureSimplified PBI model

[v-chuncz-msft]

@SteffenG,

As far as I know, there might be no elegant way than Unpivot Columns.

[SteffenG]

Thank you, Sam. Appreciate your help.

If anybody else can think of another solution, please feel free to comment.

Steffen