Need help with relationship between two tables

[UHS]

Hi All,
I am having two tables between which there is no relation plus there is no unique key to identify unique records.
COls from table A to be used

Year-Quarter

Cost Centre

OP_expense_Sub_cat

Amt_InQ_000s_Actual1

Actual Allocation1

Amt_InQ_000s_Budget1

Budget Allocation1

COls from table B

FC_LOB

Cost Centre

Function code

Act SKF%

Final Expected result from both tables

Year-Quarter

FC_LOB

Cost Centre

Function code

OP_expense_cat

OP_expense_Sub_cat

Amt_InQ_000s_Actual1

Act SKF%

Actual Allocation1

Amt_InQ_000s_Budget1

Budget Allocation1

The cost centre in both the tables are not unique and for one costcentre there are multiple function codes. i want this func code col and act skf% to be in the table A
Both table have many to many relationship created and i have even added the cols from both the table in table visual. i am using related func and lokup func to add the table b col in table A but i get errorrs for multiple values are there where single value is expected.
How can i achieve this..
Appretiate quick help

[UHS · Accepted solution]

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.

[v-dineshya]

Hi @UHS ,

Thanks for the update and happy to know you have found a solution. Mark it 'Accept as Solution'. It may help others who may be in similar scenarios.

Thank you.

[ryan_mayu]

@UHS 

could you pls provide some sample data and expected output?

[UHS]

Thanks for yuor reply, my issue got resolved 

[UHS]

Hello Ryan,
thanks for your message. 
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

[ryan_mayu]

Pls provide the sample data for table A and B separately.

[UHS]

Thanks for yuor reply, my issue got resolved.. i created a briddge table 

[d_m_LNK]

How 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?

[UHS]

Hello,
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-dineshya]

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.

[v-dineshya]

Hi @UHS ,

 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.