Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Connect column header to another table column data



I am new to Power BI and I have an issue connecting my tables. I have my data tables in a structure like below.


Table   A                                                                                                     



Table B



The above is a sample of how the values and lables are being stored. I cannot use a falt file becuase if an option is not included in the flat file, it will be missing from the reports created. I need to connect these tables together. Tranposing is not an ideal solution for me just because there are way too many rows and columns in my data table. Can you please help me to figure out whats the best way to achieve this? Do we have any way using Power Query or DAX?


Thank you!




Hi, @pjose 


It’s my pleasure to answer for you.

There is not much data in the dimension table, you can try to subdivide the dimension table, and then create corresponding relationships with the data table.

Like this:


Here is my sample .pbix file. Hope this helps.

If it doesn’t solve your problem, please feel free to ask me.


Best Regards

Janey Guo


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


Hi, @v-janeyg-msft and @amitchandak 


Thank you very much for the answers.

Those are differet options that I could possibly do.


The example I provided was a really basic one, I am basically working on survey responses with around 10 demographic questions and the rating scale questions (around 40 - 50) for each survey and these questions and answer options will be different for different surveys.


The example I showed is the way we are storing data in different tables (one database per survey) and I am looking into generalizing the approach of connecting the tables and then generating meaningful charts/tables out of them.


This is a sample .pbi file which can give an idea of what I am talking about - Sample .pbi file 


Please have a look and let me know if you have any other ideas around the same.


Thank you again!

Super User IV
Super User IV

@pjose , One way is have these three columns in table A


Age Label = maxx(filter(TableB, TableA[Age] = TableB[FieldID] && TableB[Header] = "Age"),TableB[Label])
Gender Label = maxx(filter(TableB, TableA[Gender] = TableB[FieldID] && TableB[Header] = "Gender"),TableB[Label])
State Label = maxx(filter(TableB, TableA[State] = TableB[FieldID] && TableB[Header] = "State"),TableB[Label])


The second way that join Table2[Value] with Age, Gender, and State. One will active and two will inactive. Now when you want a measure on these use a filter like TableB[Header] = "Gender" and userealtion if join is inactive



New measure = calcculate(countrows(TableA), TableB[Header] = "Gender" , userealtion(TableA[Gender],TableB[Value]))


refer for use relation

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources


Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors