Forum Discussion

pokdbz's avatar
pokdbz
Helper II
5 years ago

2 Fact table help

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, P...
Need Help
pokdbz's avatar
pokdbz
Helper II
5 years ago

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's avatar
Jihwan_Kim
Super User
5 years ago

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's avatar
    pokdbz
    Helper II
    5 years ago

    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.