How to avoind this fact fact relation

[RJOttenheim]

Wanted a report sum(amount) from fact ledger compared with the contractvalue from fact contract categorized by servicetype 

Issue is the relation between these 2 facts is n:m

[PaulDBrown · Accepted solution]

If the relationship is many-to-many, I would suggest you create a new dimension table covering the distinct Contract ID values and join the dimension table with each Contract ID field in the fact tables in one-to-many relationships. 
You can create this dimension table using:

Contract ID Table =
DISTINCT (
    UNION (
        VALUES ( Fact_ledger[Contractid] ),
        VALUES ( Fact_contract[Contractid] )
    )
)

[RJOttenheim]

I knew this one and i also think it works except in a specific case described below. 

If i read stuff about relationships people always say avoid fact-fact relationships. And i have to agree.

For instance in your example. What is a contract exists without a ledger.

In that case the sum of contract_amount would show another result compared to a visual

Show the sum of contract_amount and the sum of ledger_amount 

So in general (and in a correct star shema) these type of tables would be linked through a dimension table.

But in this specific case i dont know which dimension table as i dont have one and would not know which one to create and how it should look.

[v-stephen-msft]

Hi @RJOttenheim ,

The Cardinality option can have one of the following settings: Many to one (*:1), One to one (1:1), One to one (1:1) and Many to many (*:*). What's the meaning of n:m?

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Now suppose it's a many-to-many cardinality.

Sample table:

The measure of summing up amount group by ContractID. In the FILTER(ALLSELECTED(Fact_Ledger)..., [ContractID] in table Fact_Ledger is compared to [ContractID] in table Fact_Contract.

Sum = CALCULATE(SUM('Fact_Ledger'[Amount]),FILTER(ALLSELECTED(Fact_Ledger),[ContractID]=MAX('Fact_Contract'[ContractID])))

You need to note that ContractID is from Fact_Contract.

If you still have doubts, please provide me with some simple sample data and the expected results.

Best Regards,

Stephen Tao

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