Sum from a second lookup table

[pedroccamaraDBI]

Hello all

I have my sales fact table, (1st) with all the values that i need. This fact table is connected to clients dimension table (2nd) and this one is also connected to another dimension table, (3rd) for cost center.
In my report i want to show my sales measure by cost center, i mean, total sales from the first table, using the cost center filter from the 3rd table, like this pic

Thanks a lot all of you
Best regards

Pedro

[vanessafvg]

so your relationships are all single directional ie your fact has a many to one relationship with 2nd table and 2nd table has a many to one relatonship with table 3?  

have you set this up already can you screen shot your relationships?

firstly what is the purpose of using table 3? using cc name?

[pedroccamaraDBI]

Hello @vanessafvg 
Thank you for your message and i'll try to answer you.
My relationships are both single direction. Like this picture and not the other above.

And the purpose of using table 3 is because at some point, i believe i'm gonna add another table to table 3 and also i need to use those fields with hierarchies.
Thansk a lot
Pedro

[vanessafvg · Accepted solution]

well there are several ways to do this

1. for the purposes you can use crossfilter which will allow you to  create the bidirectional relationships in a measure rather than in your active relationships.  bidirectional relationships generally are not recommended

https://radacad.com/dax-crossfilter-function-in-power-bi-write-the-formula-both-directional-but-keep...

2. you can denormalise the dimension tables into one table 

3. you can use bidirectional relationships but crossfilter would be better

if you able to share some dummy data i can mock it up

[pedroccamaraDBI]

Thank you so much @vanessafvg for your answer.

I believe i will keep it simple and option 2 will be better, for the purpose of adding another table in the future.
Best regards
Pedro