2 Fact table help

Question

Fact 1Store&nbsp; &nbsp; Dept&nbsp; &nbsp; Promo Flag201&nbsp; &nbsp; &nbsp; &nbsp;9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Y202&nbsp; &nbsp; &nbsp; &nbsp;9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; N&nbsp;Fact 2Store&nbsp; &nbsp;Dept&nbsp; &nbsp; Sales201&nbsp; &nbsp; &nbsp; 9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000202&nbsp; &nbsp; &nbsp; 9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2050&nbsp;DimensionStore, Dept, Promo Flag joined 1 to many&nbsp;Incorrect Result I'm gettingStore&nbsp; &nbsp; Dept&nbsp; &nbsp; Sales&nbsp; &nbsp; Promo201&nbsp; &nbsp; &nbsp; &nbsp;9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; Y201&nbsp; &nbsp; &nbsp; &nbsp;9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; N&nbsp;I'm looking to get the result ofStore&nbsp; &nbsp; Dept&nbsp; &nbsp; Sales&nbsp; &nbsp; Promo201&nbsp; &nbsp; &nbsp; &nbsp;9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; Y&nbsp;I'm not sure why I'm getting duplicate rows with every value within the Promo Dimension.Does anyone have any insights or ideas of whats going on or how to resolve this?Thanks

jihwan_kim · Answer

Hi,&nbsp;pokdbz&nbsp;&nbsp;I think it is because Promo Flag Table is not filtering Fact2 table. (check the below picture's filtering direction)&nbsp;You can try to write the measure for the Sales Total like below. Then it will work properly. But be careful when using crossfilter Function in DAX measure. In this case, because the model is not that complicated to understand, it should work properly.Sales Total = CALCULATE( SUM(Fact2[Sales]), CROSSFILTER( Fact1[Store], Store[Store],Both))&nbsp;Hi, My name is Jihwan Kim. If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.Linkedin: https://www.linkedin.com/in/jihwankim1975/&nbsp;

pokdbz · Answer

Jihwan_Kim&nbsp;Thanks for the help that worked.If I wanted to add a 3rd fact table and again display the result of another dimension DSD_Flag.Fact3Store&nbsp; &nbsp; Dept&nbsp; &nbsp;DSD_Flag201&nbsp; &nbsp; &nbsp; &nbsp;9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y&nbsp;When I add the 3rd fact table its displaying the same row again with all values for DSD FlagLooking to produce this.&nbsp;&nbsp;Store&nbsp; &nbsp; Dept&nbsp; &nbsp; Sales&nbsp; &nbsp; Promo&nbsp; &nbsp; DSD Flag201&nbsp; &nbsp; &nbsp; &nbsp;9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y201&nbsp; &nbsp; &nbsp; &nbsp;9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;N&nbsp;Looking to produce this.&nbsp;&nbsp;Store&nbsp; &nbsp; Dept&nbsp; &nbsp; Sales&nbsp; &nbsp; Promo&nbsp; &nbsp; DSD Flag201&nbsp; &nbsp; &nbsp; &nbsp;9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y

jihwan_kim · Answer

Hi,I think you can try something like the below.&nbsp;&nbsp;Sales Total =CALCULATE (SUM ( Fact2[Sales] ),CROSSFILTER ( Fact1[Store], Store[Store], BOTH ),CROSSFILTER ( Fact3[Dept], Dept[Dept], BOTH ))&nbsp;Hi, My name is Jihwan Kim. If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.Linkedin: https://www.linkedin.com/in/jihwankim1975/

pokdbz · Answer

Jihwan_Kim&nbsp;Thanks for the help.&nbsp; I think I've identified why its doing this.&nbsp; But not sure what the difference is maybe you have some insight.&nbsp;Table column aggregated value of Summarization of column SLS_AMT, it has the "E" next to it.&nbsp; When I use this column it doesn't cause the row expansion&nbsp;Measure created Sales = SUM(SLS_AMT)When this measure is created is causes the extra rows to be produced.&nbsp;

Forum Discussion

2 Fact table help

4 Replies

Related content

Relationship with Fact tables

Can I filter the 1st fact table which then filters a 2nd fact table?

Manage aggregations multiple fact tables DirectLake over OneLake

fact table and dimension table

Organize the data for the fact table