2 Fact table help

[pokdbz]

Fact 1

Store    Dept    Promo Flag

201       9          Y

202       9          N

Fact 2

Store   Dept    Sales

201      9          1000

202      9          2050

Dimension

Store, Dept, Promo Flag joined 1 to many

Incorrect Result I'm getting

Store    Dept    Sales    Promo

201       9          1000    Y

201       9          1000    N

I'm looking to get the result of

Store    Dept    Sales    Promo

201       9          1000    Y

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]

Hi, @pokdbz 

I think it is because Promo Flag Table is not filtering Fact2 table. (check the below picture's filtering direction)

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))

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]

@Jihwan_Kim 

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.

Fact3

Store    Dept   DSD_Flag

201       9         Y

When I add the 3rd fact table its displaying the same row again with all values for DSD Flag

Looking to produce this.  

Store    Dept    Sales    Promo    DSD Flag

201       9          1000    Y             Y

201       9          1000    Y             N

Looking to produce this.  

Store    Dept    Sales    Promo    DSD Flag

201       9          1000    Y             Y

[Jihwan_Kim]

Hi,

I think you can try something like the below.

Sales Total =
CALCULATE (
SUM ( Fact2[Sales] ),
CROSSFILTER ( Fact1[Store], Store[Store], BOTH ),
CROSSFILTER ( Fact3[Dept], Dept[Dept], BOTH )
)

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]

@Jihwan_Kim 

Thanks for the help.  I think I've identified why its doing this.  But not sure what the difference is maybe you have some insight.

Table column aggregated value of Summarization of column SLS_AMT, it has the "E" next to it.  When I use this column it doesn't cause the row expansion

Measure created Sales = SUM(SLS_AMT)

When this measure is created is causes the extra rows to be produced.