Forum Discussion
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
| UserID | Age | Gender | State |
| 1 | 1 | 2 | 3 |
| 2 | 3 | 1 | 4 |
| 3 | 2 | 1 | 1 |
| 4 | 1 | 1 | 2 |
| 5 | 4 | 2 | 3 |
Table B
| Id | FieldID | Value | Label | Header |
| 1 | 1 | 1 | 18-25 | Age |
| 2 | 1 | 2 | 26-35 | Age |
| 3 | 1 | 3 | 36-45 | Age |
| 4 | 1 | 4 | 45+ | Age |
| 5 | 2 | 1 | Female | Gender |
| 6 | 2 | 2 | Male | Gender |
| 7 | 3 | 1 | VIC | State |
| 8 | 3 | 2 | NSW | State |
| 9 | 3 | 3 | TAS | State |
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!
3 Replies
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
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!
