Measure between two fact tables with shared dimension (indirect join)

[Anonymous]

Hi, 

I want to create a Measure between two Measures from two different fact tables that share a commen dimension. I tried the RELATED function, however it seems this works only if the tables are  directly linked.

The cardinalities:

Table1 -- Shared dimension (1:1)

Table2 -- Shared dimension (n:1)

But even with an direct join between the fact tables (1:n) the measurement columns from table1 turn into "normal" columns and can't be used for calculation anymore when using RELATED function. 

I also tried to create a measurement within the shared dimension. But RELATED function only recognizes columns from Table2... 

Any help much appreciated! 

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @Anonymous ,

I have built a data sample as shown below:

Can you please share more detail information(tables and outputs) to help us clarify your scenario?

Refer to:

https://community.powerbi.com/t5/Desktop/Calculate-Total-Sales-Value-while-taking-the-prices-product-from/m-p/157912/highlight/true#M68493

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.