Forum Discussion
Helper INeed help with relationship between two tables
Hi Everyone,
Thanks so much for your replies.
I found the solution to my problem and here is what i did.
I created a bridge table between these two tables and added al lth eunique cost centres there and then indivisually joined tables by oneto many relationship as
Table A many to one relation with Bridge table and Table B many to one relation with Table A and make it active and also filter direction to be selected as both.
Then createe 2 measures in the table 2 where i wanted to have the logic updated
Following all these steps my issue gor resolved.
Super UserHow are you trying to add the column? Within a measure on the visual or with a calcualted column on the table? The error you are getting is usually a context error where the function you wrote is expecting a single value (row context) but a column of multiple values is referenced instead of a single value.
Could you paste the code of your measure/calculated column for us to better understand what's going on?
Helper IHello,
PFB data for reference
| Table A | Table B | Table B | Table B | Table A | Table A | Table A | Table B |
| Year-Quarter | LB | CC | FC | Cat | Subcat | Amount | Act% |
| 2024-Q1 | NonCore | 100000 | 0000-AA000 | Compensation | Bonus | 7.284 | 99.6 |
| 2024-Q1 | NonCore | 100000 | 0000-AA000 | Compensation | Other Benefits | 13.7054 | 99.6 |
| 2024-Q1 | NonCore | 100000 | 0000-AA000 | Compensation | Pension Expense | 5.70776 | 99.6 |
2024-Q1 | NonCore | 100000 | 1111-AA1111 | Invest Exp | Invest Exp | -31.39126 | 0.4 |
| 2024-Q1 | NonCore | 100000 | 1111-AA1111 | Compensation | Bonus | 7.284 | 0.4 |
| 2024-Q1 | NonCore | 100000 | 1111-AA1111 | Compensation | Other Benefits | 13.7054 | 0.4 |
| 2024-Q1 | NonCore | 100000 | 1111-AA1111 | Compensation | Pension Expense | 5.70776 | 0.4 |
I have highlighted the columns to identify which is coming from table A and B. Many to many relation is created between these two tables and this is what i see in my report visual right now.
Now i want to create two logics, i.e. if the cat is Invest Exp then Act% should be 100 else as it is and Fc should be AA1010 else as it is. so expected result should be as below :
How can i achieve it.
Note: @ different tables.. Joined based on mamuy to many relation
| Table A | Table B | Table B | Table B | Table A | Table A | Table A | Table B |
| Year-Quarter | LB | CC | FC | Cat | Subcat | Amount | Act% |
| 2024-Q1 | NonCore | 100000 | 0000-AA000 | Compensation | Bonus | 7.284 | 99.6 |
| 2024-Q1 | NonCore | 100000 | 0000-AA000 | Compensation | Other Benefits | 13.7054 | 99.6 |
| 2024-Q1 | NonCore | 100000 | 0000-AA000 | Compensation | Pension Expense | 5.70776 | 99.6 |
| 2024-Q1 | NonCore | 100000 | 1111-AA1010 | Invest Exp | Invest Exp | -31.39126 | 100 |
| 2024-Q1 | NonCore | 100000 | 1111-AA1111 | Compensation | Bonus | 7.284 | 0.4 |
| 2024-Q1 | NonCore | 100000 | 1111-AA1111 | Compensation | Other Benefits | 13.7054 | 0.4 |
| 2024-Q1 | NonCore | 100000 | 1111-AA1111 | Compensation | Pension Expense | 5.70776 | 0.4 |
- d_m_LNK
Thanks for the sample data, with which columns are you relating the tables?
- UHS
Thanks for your reply, my issue got resolved by creating a bridge table.
v-dineshyaCommunity Support
Hi UHS ,
Thanks for the update and happy to know you have found a solution. You can simply post the details of your approach and mark it 'Accept as Solution'. It may help others who may be in similar scenarios.
Thank you.