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

Connect column header to another table column data

Hi,

 

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                                                                                                     

UserIDAgeGenderState
1123
2314
3211
4112
5423

 

Table B

IdFieldIDValueLabelHeader
11118-25Age
21226-35Age
31336-45Age
41445+Age
521FemaleGender
622MaleGender
731VICState
832NSWState
933TASState

        

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!

 

@needhelp 

3 REPLIES 3
Microsoft
Microsoft

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:

1.png2.png

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

 

example

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

 

refer for use relation

https://www.youtube.com/watch?v=e6Y-l_JtCq4



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

Announcements
secondImage

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