Forum Discussion

jd8766's avatar
jd8766
Icon for Helper II rankHelper II
4 years ago
Solved

Dealing with multiple fact tables?

We have a star schema in place currently (see screenshot example), however I have been tasked to add a new Fact table (the payments one) into our existing SSAS model.   I am planning on using t...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi jd8766 ,

     

    You are right.

    If you want to avoid relationship, you will need to create a measure like below:

    Measure = CALCULATE(SUM('Fact Payment'[PaymentAmount]),FILTER('Fact Payment','Fact Payment'[ContractKey] in VALUES('Fact Sales'[ContractKey])))

     

    Best Regards,

    Jay

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi jd8766 ,

 

You just need to create relationships between these tables using [ContractKey].

Then create the visual like below:

If I misunderstood you meaning, please show the expected result.

 

Best Regards,

Jay

jd8766's avatar
jd8766
Icon for Helper II rankHelper II
4 years ago

From my understanding of data modelling a fact table should never directly filter a fact table, and a bi-directional relationship could create performance issues/maybe bring back incorrect data?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi jd8766 ,

     

    You are right.

    If you want to avoid relationship, you will need to create a measure like below:

    Measure = CALCULATE(SUM('Fact Payment'[PaymentAmount]),FILTER('Fact Payment','Fact Payment'[ContractKey] in VALUES('Fact Sales'[ContractKey])))

     

    Best Regards,

    Jay