Create new column from table that is joined through another table

[Anonymous]

I have a table that does not have a direct join to a table, but is joined through another table. Ex: Billed Fees Table is joined to Time Inquiry Table on Tkpr_BilledFees_Key (Many to Many), Time Inquiry Table is joined to Currency Exchange Rate Table on Druno_CurrCode_Key (Many to One.) I have already create a calculated column in the Time Inquiry Table for the Exchange Rate. I CAN create a join on the Currency Exchange Rate Table to the Billed Fees Table on Druno (Many to One) but am getting an error there is already an active relationship - the Time Inquiry and Currency Exhange Rate tables. 

What I need to do is get the Exchange Rate into the Billed Fees Table, just like I have it in the Time Inquiry Table. I have tried to create a new column on a related field from Time Inquiry[Exchange Rate] and get the error the column doesn't exist or doesn't have a relationship to any table in the current context. I have also tried to create a new column on a related field in the Currency Exchange Rate[Exchange Rate] and get the same error as well. 

I will note that if I create a simple table, and just put the values in from all the tables, I can see them and they are all there, so I know the joins are working (see below.) I can't understand if the joins are working and if I can create the calculated column in one table why can't I create it in another?  

Can someone please advise what I am doing wrong? 

[v-cherch-msft]

Hi @Anonymous 

It seems you may try to use bidirectional filters in this article.If you need further help,could you share some simplified data sample and expected output?It would be better if you can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

For further,please check if the model in below post could help.

https://community.powerbi.com/t5/Desktop/Data-model-with-2-many-to-many-fact-tables/m-p/661572#M317843

Regards,

[Anonymous]

Hey  @v-cherch-msft ,

I tried to use the Crossfilter functionality thinking it would work but it didn't. I am attaching the file. Here is the jest of it:

I have a page Time Inquiry Metrics where I created some new columns and measures. One of them is Base Amt Ex Rate (you will see the column in the Time_Inquiry fields.) That was easy because the Time Inquiry table joined the Curr Exhange Rate Table with a One to Many on Druno Curr Code Key. 

I essentially need to do the same thing in the Billed Fees Metrics page. I need to create a column (or measure) from the Billed Fees to multiply Base Amount * Exchange Rate from the Currency Exchange Rate table. However, there is no direct join. There is a bidirectional join through the Time Inquiry table. The Billed Fees table and Time Inquiry tables' joins are Many to Many. I am also going to have the same problem when I approach the Paid Fees table. 

I am attaching the file. The table you see in the Billed Fees Metrics is showing the linked data from the Billed Fees table and Time Inquiry table, so I know the relationship is there. I am just trying to figure out how to make the column or measure work. 

Unfortunately I will need an email address in order to share the link for the file on OneDrive. Any additional help you can provide would be greatly appreciated. 

Thanks

@Anonymous 

[v-cherch-msft]

Hi @Anonymous 

Could you give some simplified assumed data to reproduce your scenario?You may paste the data here and tell me your expected output.

How to Get Your Question Answered Quickly

Regards,