Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pjose
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
v-janeyg-msft
Community Support
Community Support

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!

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.